July 8, 2010 at 4:47 pm
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.
July 9, 2010 at 10:42 pm
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
July 10, 2010 at 2:23 pm
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.
July 14, 2010 at 10:31 am
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
November 7, 2012 at 2:15 pm
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/
May 13, 2015 at 4:26 pm
@Elliott Whitlow: Thank you so much for the article. 😀 It helped a bunch to get my Execute package task set up.
Regards,
Bree
June 9, 2016 at 6:20 pm
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
December 8, 2016 at 10:22 am
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.
December 8, 2016 at 12:19 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply