Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running 1 SSIS package parallel Expand / Collapse
Author
Message
Posted Monday, February 24, 2014 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:08 AM
Points: 3, Visits: 56

Hi all, I have a package that gets data from csv file, create table in sql, and load data, I want to know if there is a way to control nu,ber of tables created and loaded? for example if the csv have 500 table names, how can I automate the package to run first 10 table names, then oncve finished loading, then run from 11 to 21? any help will be appreciated.
Post #1544419
Posted Monday, February 24, 2014 9:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:59 AM
Points: 405, Visits: 1,139
try using a for loop.

what your suggesting is possible, but not straight forward.

One possible way to do it is create a package to dynamically create a package with a dataflow for each table, based upon what you read out of the csv file.

http://technet.microsoft.com/en-us/library/ms345167.aspx


alternatively maybe you could create a 10 dataflows, dynamically set the source and destination and if for example you have 10 dataflows, but only need 9, programatically disable the 10th using an SSIS expression.



_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1544592
Posted Monday, February 24, 2014 12:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
You can create a package that performs a single table create/load/process and use variables to override table name, file name, etc. Nest the for mentioned package that has the loop operation. Not pretty, but works.

If you need something more formalized, then look into calling the package via .Net. We have a system that performs this operation with overrides for file names, etc and can run with several threads to execute in parallel. the threads pull the next available file from the queue so no waiting for the slow file to complete if the other threads have a quick operation.

Post #1544679
Posted Tuesday, February 25, 2014 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:08 AM
Points: 3, Visits: 56
Hi,
thank you for the reply,
this is what I did,
I have a data flow task that read from csv, then I likned it to FOREACH LOOP CONTAINER, inside the for each loop container I have a script component, with the script component, the script component is used to programatically build a package, do dynamic mapping and loading of data to destination, it works perfectly. Now I am currently trying to put a 4 loop containers, within each container I want to put the foreach loop container, I am only struggling with how to split records from csv to go to different FOR LOOP CONTAINERs, ie 10 per records per loop container.
Post #1544844
Posted Wednesday, February 26, 2014 1:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:51 AM
Points: 579, Visits: 1,255
If the csv format is the same why create new tables? Having a staging table which is cleared at the start of the whole import and just adding another column or two to indicate in some way which csv file that row came from and the import date/time would be more effective surely?
Post #1545245
Posted Sunday, March 9, 2014 8:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 14, 2014 3:17 PM
Points: 1,118, Visits: 2,224
Hi,

If you can use third-party solutions, check the commercial COZYROC Parallel Loop Task. The task allows you to select For Each Loop container and execute multiple iterations from the loop in parallel.


---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #1549093
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse