SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running 1 SSIS package parallel


Running 1 SSIS package parallel

Author
Message
VinceM
VinceM
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 76
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.
dave-dj
dave-dj
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2508 Visits: 1149
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)
EricEyster
EricEyster
Say Hey Kid
Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)

Group: General Forum Members
Points: 710 Visits: 520
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.
VinceM
VinceM
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 76
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.
P Jones
P Jones
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2744 Visits: 1524
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?
CozyRoc
CozyRoc
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6326 Visits: 2235
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/


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search