Data Flow Task Plus isn't doing anything
111 views
Skip to first unread message
Cristiano Guadagnino
Apr 28, 2016, 5:44:30 AM4/28/16
to COZYROC
Hi all,
I am evaluating the usage of Data Flow Task PLus.
I am developing an ETL in Visual Studio 2010. I downloaded and added the SSIS+ compnents from COZYROC's web site, and proceeded to integrate Data Flow Task Plus in my ETL.
I have a connector to DB/2 (source) and a connector to SQL Server (destination). I have to read records from several tables on DB/2 and write the records to a single table on SQL Server.
All source tables have the same columns, and the destination table also have the same set of columns.
I use a foreach loop to read table names from a SQL Server table, where I also have a few columns that tell me how to filter the records from the source tables.
Then I populate a variable with the sql statement that will be executed on the source table.
The Data Flow Task has a source of type "SQL command from variable" which executes the sql statement I have just built, and a destination of type "OpenRowset Using FastLoad From Variable" which writes the records to the destination table (table name resides in another variable).
I have used the THUNK_COLUMN for the columns mappings, and then I configured the Data Flow Task Plus component by setting both source and destination as "Enabled", to use the dynamic mapping.
When I run the package, it gets to the Data Flow Task Plus component and then sits there indefinitely with the "running" icon overlay. If I enter the data flow (i.e. I switch on the Data Flow tab) the source and destination components do NOT have the "running" icon overlay.
By using SSMS, I see that the SSIS package is connected and running but it is not doing anything.
What am I doing wrong?
Thank you in advance.
Cris
I am evaluating the usage of Data Flow Task PLus.
I am developing an ETL in Visual Studio 2010. I downloaded and added the SSIS+ compnents from COZYROC's web site, and proceeded to integrate Data Flow Task Plus in my ETL.
I have a connector to DB/2 (source) and a connector to SQL Server (destination). I have to read records from several tables on DB/2 and write the records to a single table on SQL Server.
All source tables have the same columns, and the destination table also have the same set of columns.
I use a foreach loop to read table names from a SQL Server table, where I also have a few columns that tell me how to filter the records from the source tables.
Then I populate a variable with the sql statement that will be executed on the source table.
The Data Flow Task has a source of type "SQL command from variable" which executes the sql statement I have just built, and a destination of type "OpenRowset Using FastLoad From Variable" which writes the records to the destination table (table name resides in another variable).
I have used the THUNK_COLUMN for the columns mappings, and then I configured the Data Flow Task Plus component by setting both source and destination as "Enabled", to use the dynamic mapping.
When I run the package, it gets to the Data Flow Task Plus component and then sits there indefinitely with the "running" icon overlay. If I enter the data flow (i.e. I switch on the Data Flow tab) the source and destination components do NOT have the "running" icon overlay.
By using SSMS, I see that the SSIS package is connected and running but it is not doing anything.
What am I doing wrong?
Thank you in advance.
Cris
Ivan Peev
Apr 28, 2016, 6:58:43 PM4/28/16
to COZYROC
Hi Chris,
If your source and destination columns are the same, there is no need to use COZYROC Data Flow Task Plus. You can accomplish your process using the regular Data Flow Task when having static columns.
Cristiano Guadagnino
Apr 29, 2016, 2:37:39 AM4/29/16
to COZYROC
Ivan, thank you for your reply.
Unfortunately this is not my case.
I kept it simple to avoid having to publish a lengthy description. What the package really does is copying a set of (numbered) tables from DB/2 to a single table to SQL Server, but it does this in a loop: I have various sets of tables, each of which has to be copied to a different single table on SQL Server.
Each set of tables has the same columns as its destination table, but the columns are different among different sets. That's why I cannot use the standard SSIS Task Flow component.
So to restate it: what am I doing wrong, that Data Task Flow Plus isn't doing anything?
Unfortunately this is not my case.
I kept it simple to avoid having to publish a lengthy description. What the package really does is copying a set of (numbered) tables from DB/2 to a single table to SQL Server, but it does this in a loop: I have various sets of tables, each of which has to be copied to a different single table on SQL Server.
Each set of tables has the same columns as its destination table, but the columns are different among different sets. That's why I cannot use the standard SSIS Task Flow component.
So to restate it: what am I doing wrong, that Data Task Flow Plus isn't doing anything?
Ivan Peev
Apr 29, 2016, 7:07:24 AM4/29/16
to COZYROC
Cris,
Did you review your execution log? Do you see any errors there ?
Cristiano Guadagnino
Apr 29, 2016, 7:25:40 AM4/29/16
to COZYROC
Nothing unusual there. I can post it if you like.
Cris
Cris
Cristiano Guadagnino
May 18, 2016, 6:58:54 AM5/18/16
to COZYROC
Replying to myself, to document the solution I found.
I hope it will be of some help to others having the same problem.
First of all: the cause of the problem is that DFTP is unable to correctly retrieve the columns from DB2 when the table name is an alias.
I was dealing with aliases, so I had to put up some catalog queries to retrieve the physical table and then feed that one to DFTP.
Second: even after doing this I had problems with DFTP apparently stuck doing nothing.
It turned out that DFTP needs some time to retrieve something from DB2 (not the column names, because I can see those being discovered quite fast), and this time is directly proportional to the amount of rows that need to be transferred. Since I am dealing with some tables with hundreds of millions of rows (the first one had 226 millions of rows), I was giving up before DFTP actually began copying rows.
Fortunately on one of my tries I forgot to stop the task while I was looking for hints on the web, and to my surprise after more than 10 minutes of "doing nothing" it began copying.
Once it starts it is actually quite fast.
Hope this helps.
Cris
I hope it will be of some help to others having the same problem.
First of all: the cause of the problem is that DFTP is unable to correctly retrieve the columns from DB2 when the table name is an alias.
I was dealing with aliases, so I had to put up some catalog queries to retrieve the physical table and then feed that one to DFTP.
Second: even after doing this I had problems with DFTP apparently stuck doing nothing.
It turned out that DFTP needs some time to retrieve something from DB2 (not the column names, because I can see those being discovered quite fast), and this time is directly proportional to the amount of rows that need to be transferred. Since I am dealing with some tables with hundreds of millions of rows (the first one had 226 millions of rows), I was giving up before DFTP actually began copying rows.
Fortunately on one of my tries I forgot to stop the task while I was looking for hints on the web, and to my surprise after more than 10 minutes of "doing nothing" it began copying.
Once it starts it is actually quite fast.
Hope this helps.
Cris
Reply all
Reply to author
Forward
0 new messages