can I get the Parent task in a custom data flow task?

I'm building a custom data flow task to handle errors in a generic way in C#. I want to output the Task name (the name of the Data flow) as well as the Task name that is inputting data into my custom task.

Is there a way to get both the task name and the previous data flow task name that's inputting the data?

THanks in advance,
-Chris

PS, the VariableContainer didn't seem to contain any of the system variables- ex. the below statement returned false.

this.VariableDispenser.Contains("System::SourceName")

Are you talking about a control flow task or a data flow component? For a dataflow component you can not get any information about any other component other than yourself.

As for the variable dispenser, unless you have added variables to it via the lock methods then it contains no variables. Additionally, there is no such system variable as SourceName.

Thanks,
Matt|||I am talking about a dataflow component.

Do I have to add the system variables to the VariableDispenser?

Thanks for your help Matt,
-Chris|||Yes, you have to add all variables to the variable dispenser in the form of the lock methods. Otherwise the variable dispenser is empty.

Matt|||

I added the System::SourceName variable to the custom data flow component by using LockOneForWrite, but gave an error that that variable doesn't exist.

Is there a place where I can find a listing of the system variables available at the data flow component level?

|||As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt|||

Matt David wrote:

As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt

Just to clarify Matt's point...SSIS actually does have a System::SourceName variable but it is only available in the eventhandler container. You can't reference it in your control-flow.

-Jamie|||I believe you can get hold of the component next to it in the flow, by looking at one of the input collections. However you can't find out the component that added a column to a flow.

can I get the Parent task in a custom data flow task?

I'm building a custom data flow task to handle errors in a generic way in C#. I want to output the Task name (the name of the Data flow) as well as the Task name that is inputting data into my custom task.

Is there a way to get both the task name and the previous data flow task name that's inputting the data?

THanks in advance,
-Chris

PS, the VariableContainer didn't seem to contain any of the system variables- ex. the below statement returned false.

this.VariableDispenser.Contains("System::SourceName")

Are you talking about a control flow task or a data flow component? For a dataflow component you can not get any information about any other component other than yourself.

As for the variable dispenser, unless you have added variables to it via the lock methods then it contains no variables. Additionally, there is no such system variable as SourceName.

Thanks,
Matt|||I am talking about a dataflow component.

Do I have to add the system variables to the VariableDispenser?

Thanks for your help Matt,
-Chris|||Yes, you have to add all variables to the variable dispenser in the form of the lock methods. Otherwise the variable dispenser is empty.

Matt|||

I added the System::SourceName variable to the custom data flow component by using LockOneForWrite, but gave an error that that variable doesn't exist.

Is there a place where I can find a listing of the system variables available at the data flow component level?

|||As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt|||

Matt David wrote:

As I mentioned in my original post, there is no such system variable. You can look in the designer to see all the system variable that are available.

Matt

Just to clarify Matt's point...SSIS actually does have a System::SourceName variable but it is only available in the eventhandler container. You can't reference it in your control-flow.

-Jamie|||I believe you can get hold of the component next to it in the flow, by looking at one of the input collections. However you can't find out the component that added a column to a flow.

Can a Data Flow Task mimic Bulk Insert?

Hi Guys,

The way I understand a Data Flow Task is that it inserts the rows from the source to destination one by one. Is there a way to make it act like a bulk insert task? We have been experiencing performance issues when inserting a lot of rows from one table to another. If there's no way to actually do it, can a bulk insert task functionality be scripted? Coz what I need is a table to table insert, and the bulk insert task only accepts data files as sources.

Thanks!

Kervy

What destination object are you using? The OLEDB Destination and the SQL Server destination both enable bulk insert options for SQL Server.

How many rows are you inserting?

Donald

|||

Oh you mean to say, by default, an OLEDB source inserts to an OLEDB Destination in bulk? We are only inserting a couple of hundred thousand rows, but the source table is in the US. destinations are in Europe and Asia Pacific, so we need to optimize this as much as we can given the proximity of the servers.

We are using OLEDB source and OLEDB Destination

Thanks

|||

If you need to performance tune your data-flow then you should read a whitepaper that I have mentioned here: http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx

There's also more useful information here: http://blogs.conchango.com/jamiethomson/archive/2005/04/20/1319.aspx

Note that SSIS does not insert records one at a time - that would be a performance killer. The SSIS pipeline processes data in buffers and the contents of each buffer is inserted at the same time! A large part of performance tuning your application is finding the optimum buffer size.

Note that in your scenario the bottleneck is likely to be the georgraphic disparity of the source and destination, not how fast SSIS can process it. Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require.

-Jamie

|||

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

|||

KervyChoa wrote:

hi Jamie,

Thanks for that response. I will read more on how to performance tune my data flow. One accepted cause for the horrible execution time is the distance between servers (destinations are in 2 different continents!), that is why we are trying to figure out if there are other ways to improve on this. We tried testing with a destination server in the same area, and it took about 9 minutes.. compared to the 16 hours it took when the target was Asia Pacific.. we may have to accept that performance issue for now..

total number of rows to be inserted = 200,000

total number of columns = 167

Exactly, hence my previous comment "Try and reduce the amount of data that is coming over the wire if possible - i.e. Filter the data at source and only select columns that you require."

The point here is to reduce the amount of data coming over the wire because that is quite obviously the bottleneck in your scenario.

-Jamie

|||I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?|||

KervyChoa wrote:

I tried using perfmon to see what can be done, but I can't find the SQL Server: SSIS Pipeline Performance object in perfmon.. Am I missing something here?

As we have already determined (twice) the bottleneck is not within the SSIS pipeline so looking at this perf counter is not going to help you.

Unless you pick one of the machines up and move it to a different continent, or improve the speed of the link between them, you're not going to see any eprformance improvement.

-Jamie