Package configurations stored in the database

If the configurations are stored in the database then does the package pick up the configuration as and when required or it a one time pick when the package is executed.

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

Thanks for your time.

$wapnil

They are a one time pick up at the beginning of execution. Look at the "Execution Results" of one of your packages.|||

How to look at the execution results?

Thanks,

$wapnil

|||

spattewar wrote:

What I plan to do?

When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.

You will not be able to change the table content within the same package that will use it, becasue what Phil has just said.

But, why would you want to change it that way....actually where does that script will get the values from?

Have you looked to the Dtexec SET option to assign values to the package properties instead?

|||

spattewar wrote:

How to look at the execution results?

Thanks,

$wapnil

It's a tab in Visual Studio.|||

I think I got it all wrong. Let me try to explain

I have developed a single package and have stored the configuration details in the database in the table SSIS configurations. Now this package does the task of FTP connect, download files and upload into a destination table in the database. Now I want this package

1) to be able to connect to different FTP servers in a sequence one after the other. The FTP connection details are stored in one more table FTP_Details in the database.

2) OR be able to run multiple instance of this package at the same time, each connecting to different FTP servers and downloading files.

Could you kindly provide your inputs.

Thanks for your responses.

$wapnil

|||

A simple way may be to have a master package with a ForEach loop container and an Execute package task inside. The ForEach loop would iterate through the rows in FTP_Details table to get FTP connection details, and place it/them into a variable(s). Then the Child package will use Parent variable Package configuration to receive the proper connection details on each iteration.

Notice that this approach will not execute the package in parallel.

|||Rafael's got it covered, but in case you need more information (again, the forum search is your friend), here's a link on this very topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=729225&SiteID=1|||

Thanks Phil and Rafeal. I got the approach on how to do it now.

just one more question.

If I want to run multiple instances of this package at the same time then what should be the best approach.

again, thanks for your time.

$wapnil

|||You can't run the same package multiple times at once UNLESS you create a master package that calls the same package under an Execute Package task multiple times. Even then though, I'm not sure that's a good idea given that the packages will all share the same GUID (because they are the same!).

I don't understand why you'd want them to run at the same time, when kicking one package off in a foreach loop would be best. If you must run the same package multiple times then perhaps you'd be better off copying the package (physically) and then changing its GUID.

My only concern is how can you run the same package concurrently with another instance of itself without having one step on the other's toes?|||

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

|||

spattewar wrote:

I would used a foreach loop if it would have suffice our requirement.

One of the requirement could be that we want to download files from 4 different FTPs at the same time and process the files. does this call for creating four different packages , one for each FTP?

Can I not use the dtsexec utility and run the package multiple time by passing different arguments?

Thanks for your time.

$wapnil

Yes, calling dtexec would work just fine. If you want to make your FTP site list database driven, then no, it won't help. UNLESS, you passed in a key to dtexec or something, and that key matches up to a row in the database.

What we've been talking about here is how you can create one package which will loop through a table to setup the FTP connection to as many sites are listed in the table.

There are numerous ways to do this, of course.|||

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

|||

spattewar wrote:

Ok understand that.

Then maybe I can save the package configuration into an xml file and the provide that xml file as an input to the dtsexec. This way I can run the same package by passing different configurations files.

What do you say?

Thanks for your time.

$wapnil

Sounds okay to me, if that's what you want to do... I still question the need to run them at the same time.... If you're downloading gigs of data, I'm not sure that you'll get any faster results by running them concurrently versus serialized. The data pipe can only move so much data. So, with that said, you may want to look to build a more modular approach by using a database table to drive your connections.

However you decide, I think you've got enough information to make some progress.|||ofcourse I have. Thanks