Running 1 SSIS package parallel

Question

Post reply

Running 1 SSIS package parallel

VinceM

SSC Veteran

Points: 279
More actions
February 24, 2014 at 4:04 am

#282787

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dave-dj SSChampion Points: 11297 More actions · Answer 1

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 SSCrazy Points: 2974 More actions · Answer 2

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 SSC Veteran Points: 279 More actions · Answer 3

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 SSChampion Points: 12361 More actions · Answer 4

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 One Orange Chip Points: 28499 More actions · Answer 5

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/