Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Executing multiple instances of SSIS package in parallel Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 4:47 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, February 5, 2015 12:41 PM
Points: 400, Visits: 343
What exactly I want to implement is like this -

I have a file watcher which kicks the SSIS packages when ever a flat file arrives into the Source Folder.

When ever the a new file arrives SSIS packages has to process the file by taking the parameters from the configuration file. (Database name, Servername of the Destination table are in Config file)

Meanwhile, another flat file is placed in the source folder and another instance of the same SSIS package needs to be used to process this file.. and so on...

every time the package has to take the parameters from the configuration file.

please help me in solving this issue.

Thanks in advance.


Post #949655
Posted Friday, July 9, 2010 10:42 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:47 PM
Points: 73, Visits: 184
First let us know below details.

1. how you are activating/running package means, are u using sql job or commandline command or autosys or esp or maestro etc
2. are ur source filenames same all the time? or differs?
3. ...


Each new run of the package will create a new instance, it will use same config file every time, so you can run multiple instances.

if source file name is same then, move file to a working folder and then run ur package

you can run commandline command each time when you see file in source folder...it works in separate process...

RB
Post #950308
Posted Saturday, July 10, 2010 2:22 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Thursday, February 5, 2015 12:41 PM
Points: 400, Visits: 343
PaVeRa22 (7/9/2010)
First let us know below details.

1. how you are activating/running package means, are u using sql job or commandline command or autosys or esp or maestro etc
2. are ur source filenames same all the time? or differs?
3. ...


Each new run of the package will create a new instance, it will use same config file every time, so you can run multiple instances.

if source file name is same then, move file to a working folder and then run ur package

you can run commandline command each time when you see file in source folder...it works in separate process...

RB


1. I am running the SSIS package using SQL agent job. (but I want to run many jobs at the same time which calls the same SSIS package, but the configuration file values should be changed)
2. Every time the source file name is different. (it will have _timestamp)
I want to include flat file name and Destination table name (predefined) using configuration file.
Post #950378
Posted Wednesday, July 14, 2010 10:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 17, 2015 11:50 AM
Points: 6,050, Visits: 5,314
anand_vanam (7/10/2010)
1. I am running the SSIS package using SQL agent job. (but I want to run many jobs at the same time which calls the same SSIS package, but the configuration file values should be changed)
2. Every time the source file name is different. (it will have _timestamp)
I want to include flat file name and Destination table name (predefined) using configuration file.
1. Can't, Agent will run a SINGLE instance of a job at a time.
2. The package needs to be smart enough to pick this out.

You might look at:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70346/

CEWII
Post #952515
Posted Wednesday, November 7, 2012 2:15 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 180, Visits: 735
anand_vanam (7/10/2010)


1. I am running the SSIS package using SQL agent job. (but I want to run many jobs at the same time which calls the same SSIS package, but the configuration file values should be changed)
2. Every time the source file name is different. (it will have _timestamp)
I want to include flat file name and Destination table name (predefined) using configuration file.


1. You can run the exact same package simultaneously. What you can't do is execute a job while it's currently running. I can set up a job to run package anand_vanam.dtsx. I can set up a second job to run the same package, and they'll run simultaneously. So yes you can run many JOBS which calls the same SSIS Package.
2. It looks like you're looking for a way to execute a package with dynamic parameters. There are many ways to do this. This might be a good start: http://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/
Post #1382175
Posted Wednesday, May 13, 2015 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 13, 2015 4:29 PM
Points: 1, Visits: 1
@Elliott Whitlow: Thank you so much for the article. It helped a bunch to get my Execute package task set up.
Regards,
Bree
Post #1685294
Posted Thursday, June 9, 2016 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 9, 2016 6:20 PM
Points: 1, Visits: 3
Elliott! That's exactly what I was looking for. I knew it was possible; just hadn't dug into it yet.
I'm going to take something like this and divide the work according to the DBA's analysis about server demand. Meaning: Some times during a 24 hour cycle the demand may be such that the DBA wants to reduce the load and can throttle it by reducing the number of simultaneous packages running. The quantity of servers will remain the same; just the number of parallel packages running.
Step1: get a list of remote servers with Row_Number() OVER (Partition BY ServerName Order by ServerName) RowNum.
Step2: get usage level that DBA wants to use. This will be a number between 1 and probably 10.
Step3: Take the total number of servers and divide it by Step2
Step4: Assign ranges to the number of variables DBA chose to use. While intCnt <= # etc. etc.
Step5: Where you split out the processes I'll put an expression to ensure only the ranges identified are allowed to run.
Step6: Run stress tests on ranges between 1 and 10 to see just how much the network and servers can handle without negatively impacting the end-user experience.

Of course, there will be intRange1Begin, intRange2Begin, etc.
This means I can process as many servers as the network can handle and loop as many servers, within each range, as I need to.

My Question: Do you have any recommendations for tracking all of the work that is going on? Can these individual threads use the same log file or will I have to build one for each process running and then combine them in the archive process at mid-night?
I currently have a flat file(comma delimited) capturing the Steps and work completed so, god forbid, I have to find something I'll at least have the records logged. I was thinking about archiving the log each night at mid-night so it doesn't get too big and slow down the overall processing.

Thank you very much for the visuals. A picture does say 1000+ words.

James



Post #1793582
Posted 2 days ago @ 10:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:19 AM
Points: 1,195, Visits: 3,288
1. You can run the exact same package simultaneously. What you can't do is execute a job while it's currently running. I can set up a job to run package anand_vanam.dtsx. I can set up a second job to run the same package, and they'll run simultaneously.


This situation seems clear to me. But what if I want to run the same package from within the same job but would be called from another thread. For example, I want to create a package that produces a TSQL recordset based on a variable passed in. There would be multiple procedures in the parent package that would call child packages, several of which would have this one package and each one passing in a different variable. Would this work?

I know I can test it, and if no one is sure of the answer I will soon enough, but I'm trying to think my way through a new ETL process at the moment. I read something on modularity in SSIS the other day and it got me thinking about this possibility.



Post #1841359
Posted 2 days ago @ 12:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:16 PM
Points: 180, Visits: 735
Yeah, you're going to have to test that out. My reply was most assuredly for SSIS 2008 as it was posted 4 years ago.
Post #1841404
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse