February 22, 2010 at 1:32 am
We have a system where files are uploaded daily into the database. For this, we have a SQL job which has approx 40 SSIS execution steps -a separate SSIS has been written for each file due to different file structure and format.
Is it possible to execute the SSIS steps conditionally? If the front end indicates which files need to be uploaded, can we invoke only the relevant SSIS and skip the rest?
February 22, 2010 at 7:09 am
I don't think you can do what you want in SQL Server Agent, at least not easily.
Assuming the files are all in the same directory you'd be better off using SSIS to conditionally call a package or process based on the file name, again assuming the files are named differently. For example you setup a for each loop using the FILE enumerator and then you have precedence constraints that send the processing to the correct process based on file name. Something like what Tim Mitchell describes in this blog post. The blog post is an example, NOT exactly what you would need, but it should point you in the right direction.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 22, 2010 at 9:03 am
I think Jack is right on. I was thinking about a parent package that conditionally calls the packages would be a better solution than a 40 step job. The package will still have 40 steps but you can have a LOT more control.
CEWII
February 22, 2010 at 8:42 pm
Thanks for your responses! Some more information about our process.
Currently, each SSIS being called from the job checks for the presence of a particular file(each file has a different name) in a common shared folder. If the file doesn't exist, it quits. Even if the file is not present in the shared location, this job step takes 20-30 seconds. Due to this, we end up wasting a lot of time if only a few files are present in the location.
All SSIS packages are deployed in the SQL Server(msdb).
While calling the SSIS from the job, we provide the XML configuration file location which the SSIS should use. There are a few files which use the same SSIS package but use a different dtsconfig file.
From your responses, it seems that we need to look at the option of a master SSIS package calling the individual packages based on the presence of files in the shared folder.
In this master package, we need to loop through the files in the shared folder and invoke the appropriate SSIS based on the file name. There would be approx 40 packages to choose from.
The problem with this approach is that we can't specify the dtsconfig file to be used for each of these 40 packages.
Also, since we are deploying the packages using a batch file which invokes the command line dtutil utility, we can't specify dtsconfig files during deployment as dtutil doesn't have this option.
Any ideas?
February 23, 2010 at 10:04 am
I was thinking about a solution while driving accross my state this morning..
I would probably put a For-Each-Loop loop in and have it loop through the files. Inside the F-E-L I would have at least 2 other coponents. The first is an EXEC SQL task where I would take the filename and use that to go lookup an SSIS package name, I would store the File Name/Package Name relationship in a table. I would then use the EXEC PACKAGE task to go call that package.
At first glance this is about all your master package is. There is a shade more plumbing but this is really close.
CEWII
February 24, 2010 at 7:51 pm
The master SSIS package approach seems to be working! Thank you very much!
The main issue we had was related to the dtsconfig file which each SSIS package used. The dtsconfig files had the connection information and the file name which the package should load.
We now have a single dtsconfig file used by the master SSIS package. While calling the individual package, the master SSIS package passes it the connection information and file name in variables.i.e. We have configured the individual package to get the variables populated from the parent package.
February 24, 2010 at 8:16 pm
Good method.
CEWII
February 25, 2010 at 4:42 am
Another thing to consider is that the "for each" loop should continue processing files even if any Execute Package fails due to data issues. I made the ForceExecutionResult = Success for the "for each" loop container to achieve this. Is there a better way to do this?
February 25, 2010 at 7:34 am
You might turn up the number of errors allowed.
CEWII
March 4, 2010 at 4:43 am
Just wanted to mention that the master SSIS approach gave us a tremendous jump in terms of performance. The SQL job which used to take around 17-18 minutes no matter how many files were placed in the shared folder now takes less than 5 minutes even if all files are present in the shared folder. Thanks again!
This dramatic improvement is attributed to 2 things:
1) Only execute the SSIS packages for which the files are present in the folder.
2) SSIS loading times seems to have improved in the master SSIS approach.
It is the second point which I need an answer on. Earlier, when we had the SQL job with approx 40 SSIS steps, those SSIS steps would take approx 25 seconds just to load the package from SQL Server(msdb) and upto 5 seconds for execution depending upon presence of file in the folder and size of file. Now, when we have the Execute Package task within the for each loop container in the master SSIS, the package loading time has come down drastically - if it were the same, we wouldn't have such a big difference in job run times.
Can anyone explain why loading the package is much faster in the master SSIS approach compared to the old SSIS execution job step approach?
March 4, 2010 at 7:38 am
My first thought would be that it takes some time to instantiate and start each whole new execution environment. Which is what was happening with a 40 step SQL Agent job. Since you are already in an execution environment it doesn't have to do as much work, multiply that by 40..
CEWII
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply