Package loses configuration files!

I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any ideas? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!

Are you using SQL2005/Express SP1? As I know there used to be a known issue with SP1: if you use configuration file to deploy SSIS package, the referenced file names in the configuraiton file will be change to lowercase. Since XML file is case-sensitive, the files can't be found if the name does not exactly match. Not sure whether there is any hotfix available now.|||We are using SQL 2005 Enterprise. I believe it is SP1 as well. Will see if naming everything in lower case only fixes it up. Thanks.sql

Package is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

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

Package is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

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

sql

Package doesn't finish, but no error

I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.

The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?

Any hints would be greatly appreciated.Hi Lee Gillie,

You might consider using a "For-each loop" instead with a variabel that changes value for each iteration...

Regards
Simon
|||Thanks Simon for considering this. I wasn't specific enough for you to see the 4 flags control running of 3 distinct packages, where one package is run with differening parameter setups. It would seem I would still need this kind of condition logic on the precedence within the loops to set up for each, even if there were but a single execute package task, and it is the condition on precedence that seems to be messing me up. It is not clear how the loop would avoid the need to have precedence control logic. Your response seems to presume that a simple expression to evaluate a boolean variable on a precedence does not work, and we are trying to avoid that? But perhaps my vision of implementing your idea is not right?

Thanks again, - Lee

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

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

Package Configurations

Hi,

I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.

Thanks

With the above in mind, my end achievement would be to have the same packages deployed to my test and live environment at the same time but when I execute the packages in the test environment, it will use the connection string to the test environment and likewise when I execute the packages in the live environment, it must use the connection string to the live environment.

So, effectively, I am going to have a configuration file containing the connection string on both the live and test server. The packages therefore need to point to the respective files. Has anyone does something similar?

|||

Not sure is that is the reason of your issue; but BIDS will pickup changes to the environment variable only after close it and open it back.

The approach that you described in your second post is pretty common when you have 2 environments in separete machines. I don't see an issue on that.

Rafael Salas

|||Okay, sorted that out. Another issue that I have identified however is that the connection string that is now used seems to depend on who you have logged in to SSIS as (serious problem). For example, lets say on my test server, the configuration file is specifying 'Test' as the server name to use in the connection string. When I go to the physical server and get onto SSIS and check what the connection string is, it is correctly using 'Test' as the server name. Now, if I have to connect to SSIS on the test server from my machine and check what server name is been used in the connection string, it is showing the original server name that the package was deployed with (this is different to the server name specified in the config file on the test server). Do you know if this is a bug in SQL 2005 or am I missing something here?|||

Not sure if i am understanding this correctly. Anyway, when you use package configurations for setting up values at run time; they exist just at that time (at run time); whatever value are stored in the package the last time it was saved will be always there.

Rafael Salas

Package configuration, Parent-child packages and Job scheduling

Hi,

I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.

Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?

Regards
Simon
Hi,

Seems like noone has an answer for this.

Isn't it a fundamentally important that changes in configuration files automatically apply to packages that uses them. Am I the only one with this problem?

Regards
Simon
|||I haven't observed it myself. This only occurs when you are running from the SQL Agent? Does it work properly when you run from the command line?|||

When the package is loaded it will lok at the config settings. If you aren't passing entries from the parent to the child then it will get them from the config file (where are you holding the config settings - sounds like xml?).

You seem to be suggesting the parent package gets the correct values but the child packages get old values. I've not seen a package ignore a config file - are you sure the packages are reading the correct files? Are the files on the same server (where the packages are being run)?

Opening the package and saving it again shouldn't affect the config entries unless you look at them - in which case they will be overwritten with the values in the package.

|||

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

If the package is running with the default values, maybe it is not able to find the configuration file. Are you sure it is in an appropriate location? Have you tried explicitly setting the configuration file via the /ConfigFile switch?

|||Thanks both to NigelRivett and jwelch for your suggestions and I'm sorry that I've havn't had the time to reply.

When I return from holiday I will investegate futher...
|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

I spoke with mnguyen offline and we determined that it was an issue with using a relative path to the configuration file.

Package configuration, Parent-child packages and Job scheduling

Hi,

I've found this problem that when I change settings in my configuration file it does not automatically apply to all child packages which uses the same configuration file if run from a job in SQL Server Agent. I need to open the package and save it again from BIDS. I use one "load group" package to execute all other packages.

Is there a way from the job configuration to set a setting so the package allways will have the newest configuration?

Regards
Simon
Hi,

Seems like noone has an answer for this.

Isn't it a fundamentally important that changes in configuration files automatically apply to packages that uses them. Am I the only one with this problem?

Regards
Simon
|||I haven't observed it myself. This only occurs when you are running from the SQL Agent? Does it work properly when you run from the command line?|||

When the package is loaded it will lok at the config settings. If you aren't passing entries from the parent to the child then it will get them from the config file (where are you holding the config settings - sounds like xml?).

You seem to be suggesting the parent package gets the correct values but the child packages get old values. I've not seen a package ignore a config file - are you sure the packages are reading the correct files? Are the files on the same server (where the packages are being run)?

Opening the package and saving it again shouldn't affect the config entries unless you look at them - in which case they will be overwritten with the values in the package.

|||

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

If the package is running with the default values, maybe it is not able to find the configuration file. Are you sure it is in an appropriate location? Have you tried explicitly setting the configuration file via the /ConfigFile switch?

|||Thanks both to NigelRivett and jwelch for your suggestions and I'm sorry that I've havn't had the time to reply.

When I return from holiday I will investegate futher...
|||

mnguyen wrote:

Hi,

I have the same problem with configuration file being ignored when executed ssis package via sql agent job. Have you found the solution to the problem yet?

regards,

mnguyen

I spoke with mnguyen offline and we determined that it was an issue with using a relative path to the configuration file.

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Package Configuration wizard:-SQL Configurations Configuration filter not working

Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showingis there any known workaround or forced refresh I can do

thanks in advance Dave

Background:

SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go

http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx

Wizard results:

Name:
ETL

Type:
SQL Server

Connection name:
ETLConfiguration

Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.

Configuration table name:
[dbo].[SSIS Configurations]

Configuration filter:
PT_CUST_ABR

Target Property:
\Package.Variables[User::gsPreLoad].Properties[Value]
\Package.Variables[User::gsPostLoad].Properties[Value]
\Package.Variables[User::gsLoad].Properties[Value]
\Package.Variables[User::gsFlatFilename].Properties[Value]
\Package.Variables[User::gsFileName].Properties[Value]
\Package.Variables[User::gsCDOMailTo].Properties[Value]
\Package.Variables[User::gsCDOMailSubject].Properties[Value]
\Package.Variables[User::giRecordCount].Properties[Value]
\Package.Variables[User::giFileSize].Properties[Value]
\Package.Variables[User::giBatchID].Properties[Value]
\Package.Variables[User::gdFileDateCreated].Properties[Value]
\Package.Connections[MyDatabase].Properties[ServerName]
\Package.Connections[MyDatabase].Properties[InitialCatalog]

USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

I have just tried this scenario and cannot reproduce it. I added several SQL configurations and all worked fine.

I wonder if you could try to repro, while running SQL Profiler - that way you may discover if there are any issues in updating your config table.

Donald

|||

Thanks Donald --first off I have discovered a workaround. I think its way too risky and error prone to let the wizard write the actual values into the configuration table --you can imagine the chaos in large datawarehouse that has lots of new of records to enter into in the table!!!! Lets think it through--it I have say 5 new staging tables to add to the warehouse thats 65 manual entrys I would have to make with the wizard--no way is this workable

instead I prefer to sql script my new entrys and load in on hit

The workaround is to run the SQL insert script --then go to the wizard and change the configuration filter and go through to the endat this stage if you look at the variables they wont have changed since your last entry to the configuration table

Step1: Save the package

Step2: Exit out of visual studio completely and reopen again and go back into package

Hey presto the new values in the variables have changed

Can confirm this workaround only for SQL Server 2005

I have not tested for SQL Sever 2005 SP1

cheers Dave

Package Configuration Wizard Error

I am attempting to create an XML Configuration file and I am recieving the following error once I click finish in the wizard:

Could not complete wizard actions.

Additional Information:
Could not generate the configuration file. (Microsoft.Data.TransformationServices.Wizard)

Technical Details:
===================================

Could not complete wizard actions. (Microsoft Visual Studio)

===================================

Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.OnFinish(CancelEventArgs e)

===================================

Failure exporting configuration file.
(eFreedomAS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Package.ExportConfigurationFile(String str)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)

Name:
eFreedomAS2

Type:
Configuration File

New configuration file will be created.

File name:
C:\abc.xml

Properties:
\Package.Variables[User::SourcePath].Properties[Value]
\Package.Variables[User::SourcePath].Properties[Name]
\Package.Variables[User::Packages].Properties[Value]
\Package.Variables[User::Packages].Properties[Name]
\Package.Variables[User::PackageDefNumber].Properties[Value]
\Package.Variables[User::PackageDefNumber].Properties[Name]
\Package.Variables[User::Directory].Properties[Value]
\Package.Variables[User::Directory].Properties[Name]
\Package.Variables[User::DestinationPath].Properties[Value]
\Package.Variables[User::DestinationPath].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[ConnectionString]
\Package.Connections[ViewDef].Properties[Name]
\Package.Connections[ViewDef].Properties[ConnectionString]
\Package.Connections[ValidationTestDef].Properties[Name]
\Package.Connections[ValidationTestDef].Properties[ConnectionString]
\Package.Connections[ValidationRulePackageDef].Properties[Name]
\Package.Connections[ValidationRulePackageDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleFileDef].Properties[Name]
\Package.Connections[ValidationRuleFileDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleDef].Properties[Name]
\Package.Connections[ValidationRuleDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageFileDef].Properties[Name]
\Package.Connections[ValidationMessageFileDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageDef].Properties[Name]
\Package.Connections[ValidationMessageDef].Properties[ConnectionString]
\Package.Connections[ValidationIdentifierDependency].Properties[Name]
\Package.Connections[ValidationIdentifierDependency].Properties[ConnectionString]
\Package.Connections[ValidationCalcGraphEdge].Properties[Name]
\Package.Connections[ValidationCalcGraphEdge].Properties[ConnectionString]
\Package.Connections[UserGroupFeatureDef].Properties[Name]
\Package.Connections[UserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[UserGroupFeature].Properties[Name]
\Package.Connections[UserGroupFeature].Properties[ConnectionString]
\Package.Connections[UserGroupDef].Properties[Name]
\Package.Connections[UserGroupDef].Properties[ConnectionString]
\Package.Connections[UserGroup].Properties[Name]
\Package.Connections[UserGroup].Properties[ConnectionString]
\Package.Connections[TableDef].Properties[Name]
\Package.Connections[TableDef].Properties[ConnectionString]
\Package.Connections[Source].Properties[UserName]
\Package.Connections[Source].Properties[ServerName]
\Package.Connections[Source].Properties[Password]
\Package.Connections[Source].Properties[Name]
\Package.Connections[Source].Properties[InitialCatalog]
\Package.Connections[Source].Properties[ConnectionString]
\Package.Connections[SelectListItemDef].Properties[Name]
\Package.Connections[SelectListItemDef].Properties[ConnectionString]
\Package.Connections[SelectListDef].Properties[Name]
\Package.Connections[SelectListDef].Properties[ConnectionString]
\Package.Connections[ScriptAssemblyDef].Properties[Name]
\Package.Connections[ScriptAssemblyDef].Properties[ConnectionString]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[Name]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[PageDef].Properties[Name]
\Package.Connections[PageDef].Properties[ConnectionString]
\Package.Connections[PageCalcPathSubPath].Properties[Name]
\Package.Connections[PageCalcPathSubPath].Properties[ConnectionString]
\Package.Connections[PageCalcPath].Properties[Name]
\Package.Connections[PageCalcPath].Properties[ConnectionString]
\Package.Connections[PackageLinkDef].Properties[Name]
\Package.Connections[PackageLinkDef].Properties[ConnectionString]
\Package.Connections[PackageImportTypeDef].Properties[Name]
\Package.Connections[PackageImportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierGroupDef].Properties[Name]
\Package.Connections[PackageIdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierDef].Properties[Name]
\Package.Connections[PackageIdentifierDef].Properties[ConnectionString]
\Package.Connections[PackageExportTypeDef].Properties[Name]
\Package.Connections[PackageExportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageDef].Properties[Name]
\Package.Connections[PackageDef].Properties[ConnectionString]
\Package.Connections[PackageDataItemTextDef].Properties[Name]
\Package.Connections[PackageDataItemTextDef].Properties[ConnectionString]
\Package.Connections[OnlinePagePrintedPageDef].Properties[Name]
\Package.Connections[OnlinePagePrintedPageDef].Properties[ConnectionString]
\Package.Connections[OnlinePageDefDependency].Properties[Name]
\Package.Connections[OnlinePageDefDependency].Properties[ConnectionString]
\Package.Connections[OnlinePageDef].Properties[Name]
\Package.Connections[OnlinePageDef].Properties[ConnectionString]
\Package.Connections[MaterialityToleranceDef].Properties[Name]
\Package.Connections[MaterialityToleranceDef].Properties[ConnectionString]
\Package.Connections[LineDef].Properties[Name]
\Package.Connections[LineDef].Properties[ConnectionString]
\Package.Connections[ImportTypeDef].Properties[Name]
\Package.Connections[ImportTypeDef].Properties[ConnectionString]
\Package.Connections[ImportDef].Properties[Name]
\Package.Connections[ImportDef].Properties[ConnectionString]
\Package.Connections[IdentifierStateDef].Properties[Name]
\Package.Connections[IdentifierStateDef].Properties[ConnectionString]
\Package.Connections[IdentifierRelatedDef].Properties[Name]
\Package.Connections[IdentifierRelatedDef].Properties[ConnectionString]
\Package.Connections[IdentifierPrintReferenceDef].Properties[Name]
\Package.Connections[IdentifierPrintReferenceDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[Name]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifier].Properties[Name]
\Package.Connections[IdentifierGroupIdentifier].Properties[ConnectionString]
\Package.Connections[IdentifierGroupDef].Properties[Name]
\Package.Connections[IdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroup].Properties[Name]
\Package.Connections[IdentifierGroup].Properties[ConnectionString]
\Package.Connections[IdentifierDef].Properties[Name]
\Package.Connections[IdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierDataItemTextDef].Properties[Name]
\Package.Connections[IdentifierDataItemTextDef].Properties[ConnectionString]
\Package.Connections[FormulaScriptDef].Properties[Name]
\Package.Connections[FormulaScriptDef].Properties[ConnectionString]
\Package.Connections[FormulaDef].Properties[Name]
\Package.Connections[FormulaDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupFeatureDef].Properties[Name]
\Package.Connections[FeatureGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupDef].Properties[Name]
\Package.Connections[FeatureGroupDef].Properties[ConnectionString]
\Package.Connections[FeatureDef].Properties[Name]
\Package.Connections[FeatureDef].Properties[ConnectionString]
\Package.Connections[ExportTypeRelatedDef].Properties[Name]
\Package.Connections[ExportTypeRelatedDef].Properties[ConnectionString]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[Name]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportTypeExportDef].Properties[Name]
\Package.Connections[ExportTypeExportDef].Properties[ConnectionString]
\Package.Connections[ExportTypeDef].Properties[Name]
\Package.Connections[ExportTypeDef].Properties[ConnectionString]
\Package.Connections[ExportTypeBlobTypeDef].Properties[Name]
\Package.Connections[ExportTypeBlobTypeDef].Properties[ConnectionString]
\Package.Connections[ExportFormulaScriptDef].Properties[Name]
\Package.Connections[ExportFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportDef].Properties[Name]
\Package.Connections[ExportDef].Properties[ConnectionString]
\Package.Connections[ColumnHeaderDef].Properties[Name]
\Package.Connections[ColumnHeaderDef].Properties[ConnectionString]
\Package.Connections[ColumnDef].Properties[Name]
\Package.Connections[ColumnDef].Properties[ConnectionString]
\Package.Connections[CellTypeFileTypeDef].Properties[Name]
\Package.Connections[CellTypeFileTypeDef].Properties[ConnectionString]
\Package.Connections[CellTypeDef].Properties[Name]
\Package.Connections[CellTypeDef].Properties[ConnectionString]
\Package.Connections[CellRangeDef].Properties[Name]
\Package.Connections[CellRangeDef].Properties[ConnectionString]
\Package.Connections[CellRangeCellDef].Properties[Name]
\Package.Connections[CellRangeCellDef].Properties[ConnectionString]
\Package.Connections[CellOverrideDef].Properties[Name]
\Package.Connections[CellOverrideDef].Properties[ConnectionString]
\Package.Connections[CellDef].Properties[Name]
\Package.Connections[CellDef].Properties[ConnectionString]
\Package.Connections[CategoryDef].Properties[Name]
\Package.Connections[CategoryDef].Properties[ConnectionString]
\Package.Connections[CalcPathSubPath].Properties[Name]
\Package.Connections[CalcPathSubPath].Properties[ConnectionString]
\Package.Connections[CalcPath].Properties[Name]
\Package.Connections[CalcPath].Properties[ConnectionString]
\Package.Connections[CalcGraphEdge].Properties[Name]
\Package.Connections[CalcGraphEdge].Properties[ConnectionString]
\Package.Connections[AreaDef].Properties[Name]
\Package.Connections[AreaDef].Properties[ConnectionString]

Found the issue myself. One of the variables I had unintentionally set for the package configuration was of type Object. Rightfully so, you are unable to save a variable of type object to be saved in an xml configuration file.

Package Configuration with XML file

Hi,

I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.

The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.

How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?

Any help greatly appreciated....Thank you,

Maria

Yes, that is very possible. Have you tried doing it? What problems are you having?

-Jamie

|||

Hi Jamie, for my first ssis package I created new XML dtsconfig file and lets say called it "common.dtsconfig". That all works fine. Then I created my second ssis and when I was doing Package configuration for it I selected "common.dtsconfig" file, it asked me if I want to re-use it and I said "YES". But by doing that I am not getting the path in the common.dtsconfig for my second ssis. So I deleted that connection within Package Configuration and added new with new xml file name. then I took this portion "<Configuration ConfiguredType="Property" Path="\Package.Connections[CustomerMasterTmpCurrency].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>C:\Inetpub\ftproot\customers.CSV</ConfiguredValue>
</Configuration>"

and copied to the common.dtsconfig, and went back to my second package configuration-> deleted that connection-> and re-added new connection that points to the common.dtsconfig file. That seems to be working fine.

However, when I deployed my 2 ssis they both got errors that the connection xxxx cannot be find. Each of them has a different connection. I went back to each of the package configuration deleted the old connection and re-added it........I still cannot edit as it gives me an error.................

There must be a better way to handle it and I hope you can help me with it.....Maria

Package Configuration with Environment Variable

Hi,

I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.

Here goes..

SSIS package1:

Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.

Environment variable:

Pckg_config = <location of config file which has testcon1 and testcon2>

I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2

Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig

Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.

SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.

Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.

Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.

Thanx,

TusharYou should have only two connections (devcon1 and 2) defined in your package. Set up a single configuration file that sets the two connections. When you deploy to test, you need to create a new copy of the configuration, and alter the connection strings in it to point to your test environment. So you should be using the same package file, but different configuration files in each environment.|||Thanx for responding.

I have followed exactly what you said with my prior set up. I have a devEnv.dtsconfig with devcons and testEnv.dtsConfig with testcons.

The issue is with the package. While creating the package i created it with devcons. Additionally i have testcons which do nothing but sit with the package and get deployed on the server.

If i do not add testcons.....testEnv.dtsconfig gives connection errors....when trying to locate testcons.

Let me know if you need more info...

Thanx again.

Tushar
|||You should have only two connection managers in your package. You should not have testcon1 and testcon2. To create your 2nd configuration file, copy the first configuration file into a new folder - but keep the same name. Open it in notepad, and edit the <ConfiguredValue> tag to set it to your test connection.

When you run the package for test, use the DTEXEC /CONF option to specify the 2nd configuration file.

Package Configuration using xml-file fails

Hallo,

I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration.

I choose xml configuration file as type and browse a path on our LAN to create te configuration file .

Then I select the properties of a OLEDB communication manager as Properties to Export

Doing so I obtain this configuration:Name:
JACBE_IF_CONFIG

Type:
Configuration File

New configuration file will be created.

File name:
L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml

Properties:
\Package.Connections[JACBE_IF].Properties[UserName]
\Package.Connections[JACBE_IF].Properties[ServerName]
\Package.Connections[JACBE_IF].Properties[RetainSameConnection]
\Package.Connections[JACBE_IF].Properties[ProtectionLevel]
\Package.Connections[JACBE_IF].Properties[Password]
\Package.Connections[JACBE_IF].Properties[Name]
\Package.Connections[JACBE_IF].Properties[InitialCatalog]
\Package.Connections[JACBE_IF].Properties[Description]
\Package.Connections[JACBE_IF].Properties[ConnectionString]

The system creates a XML file but when I run the package I get the following error in the output pane.

Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml".

SSIS package "FMC_People.dtsx" starting.

Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.Source: "Microsoft OLE DB Service Components"Hresult: 0x80040E21Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009.

Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation.

SSIS package "FMC_People.dtsx" finished: Failure.

I don't get it. Where do I go wrong?

I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.

Any ideas anyone? I would be most gratefull.

Kind regards,

Paul Baudouin

It won't store the password for you - you have to add it in manually. Check the contents of the file to see if the password is there (if you need it there).

By the way, you only need to store the ConnectionString property. Take out all the rest and give it a go!

-Jamie

|||

Dear Jamie,

You are a real help!

Your solution , only adding the connectionstring to the configuration file, worked when I manually added ';Password = @.@.@.@.@.' to the ConnectionString in the configurationfile.

(For security reasons I put @.@.@.@.@. instead of the real pasword.)

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Greetings,

Paul

|||

Paul Baudouin wrote:

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Unfortunately not! Encrypted configuration files is soething I have suggested and I believe it is being considered for future development.

If you want it - ask for it at Microsoft Connect. if you don't ask you don't get!

-Jamie

|||

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

|||

Paul,

how did you add it manually, this is how my connection string in the configuration file looks like

\Package.Connections[mydatabase].Properties[Password]

thanks,

kushpaw

|||

Donald Farmer wrote:

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

Absolutely. that makes sense to me Donald.

|||

Kushpaw,

As I said above, you're probably best storing the ConenctioNStrig property rather than the password. Thereafter it will be obvious where you put the password.

-Jamie

|||

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

My present config which is in sql table has config vals for FlatFile CS, two SQL DB CS I am presuming one db CS is for the SQL Destination component and the other one is to tell the package where to look for the SSIS Configurations table.

Can I remove this last Db CS store in an XML file so that when we move to production we can edit this one XML file and go on....

|||

ray_newbie_SSIS wrote:

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

Yes that makes sense. The way to do this is to use Indirect Configurations:

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Your other option is to populate the properties that can change between environments using the /SET option of dtexec. I wouldn't recommend this though - configurations are generally the way to go.

-Jamie

Package Configuration using xml-file fails

Hallo,

I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration.

I choose xml configuration file as type and browse a path on our LAN to create te configuration file .

Then I select the properties of a OLEDB communication manager as Properties to Export

Doing so I obtain this configuration:Name:
JACBE_IF_CONFIG

Type:
Configuration File

New configuration file will be created.

File name:
L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml

Properties:
\Package.Connections[JACBE_IF].Properties[UserName]
\Package.Connections[JACBE_IF].Properties[ServerName]
\Package.Connections[JACBE_IF].Properties[RetainSameConnection]
\Package.Connections[JACBE_IF].Properties[ProtectionLevel]
\Package.Connections[JACBE_IF].Properties[Password]
\Package.Connections[JACBE_IF].Properties[Name]
\Package.Connections[JACBE_IF].Properties[InitialCatalog]
\Package.Connections[JACBE_IF].Properties[Description]
\Package.Connections[JACBE_IF].Properties[ConnectionString]

The system creates a XML file but when I run the package I get the following error in the output pane.

Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml".

SSIS package "FMC_People.dtsx" starting.

Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.Source: "Microsoft OLE DB Service Components"Hresult: 0x80040E21Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009.

Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation.

SSIS package "FMC_People.dtsx" finished: Failure.

I don't get it. Where do I go wrong?

I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.

Any ideas anyone? I would be most gratefull.

Kind regards,

Paul Baudouin

It won't store the password for you - you have to add it in manually. Check the contents of the file to see if the password is there (if you need it there).

By the way, you only need to store the ConnectionString property. Take out all the rest and give it a go!

-Jamie

|||

Dear Jamie,

You are a real help!

Your solution , only adding the connectionstring to the configuration file, worked when I manually added ';Password = @.@.@.@.@.' to the ConnectionString in the configurationfile.

(For security reasons I put @.@.@.@.@. instead of the real pasword.)

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Greetings,

Paul

|||

Paul Baudouin wrote:

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Unfortunately not! Encrypted configuration files is soething I have suggested and I believe it is being considered for future development.

If you want it - ask for it at Microsoft Connect. if you don't ask you don't get!

-Jamie

|||

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

|||

Paul,

how did you add it manually, this is how my connection string in the configuration file looks like

\Package.Connections[mydatabase].Properties[Password]

thanks,

kushpaw

|||

Donald Farmer wrote:

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

Absolutely. that makes sense to me Donald.

|||

Kushpaw,

As I said above, you're probably best storing the ConenctioNStrig property rather than the password. Thereafter it will be obvious where you put the password.

-Jamie

|||

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

My present config which is in sql table has config vals for FlatFile CS, two SQL DB CS I am presuming one db CS is for the SQL Destination component and the other one is to tell the package where to look for the SSIS Configurations table.

Can I remove this last Db CS store in an XML file so that when we move to production we can edit this one XML file and go on....

|||

ray_newbie_SSIS wrote:

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

Yes that makes sense. The way to do this is to use Indirect Configurations:

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Your other option is to populate the properties that can change between environments using the /SET option of dtexec. I wouldn't recommend this though - configurations are generally the way to go.

-Jamie

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

package configuration please help

I would like to set package configuration.But dont know how to.

What i want to achieve is that i want to change connections based on test ,development and production environment of the same package...

How can i do that without me changing the connections manually to each task based on environment..

using package configuration i dont know how to...Please let me know.

http://www.sqlis.com/26.aspx|||

http://msdn2.microsoft.com/en-us/library/ms167153.aspx

http://rafael-salas.blogspot.com/2007_01_01_archive.html

|||

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

|||i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........|||FYI - you don't create the table manually, SSIS will do it for you. (That is if I'm assuming correctly what you are talking about and what Rafael wrote.)

When you create a new SQL Server based package configuration, it will create the table for you if it doesn't exist.

(I can't get to Rafael's site at the moment as we have a mis-configured proxy that blocks his blog page......)|||

sureshv wrote:

i dont find InitialCatalog & ServerName properties.. ....from the steps in this tutorial........

Those are found on database connections.|||

sureshv wrote:

>>http://rafael-salas.blogspot.com/2007_01_01_archive.html

Form this link is it like i have to create one table in sql server database which has connection string with a particular connection

so is this connection applied to all the tasks like lookups ,oledb commands,oledb destinations etc...and in my cast the text fine connection dosent change at all....so what do i do...

Phil is right, the table is created by SSIS, if it does not exist. Every row in that table represent a configuration value to be applied to a specifc property in a package's object. A row in that table looks like

ConfigurationFilter ConfiguredValue PackagePath ConfiguredValueType DBConnection-MARINER_EDW Data Source=xxxxx;Initial Catalog=xxxx;Provider=SQLNCLI.1;Integrated Security=SSPI; \Package.Connections[MARINER EDW DS].Properties[ConnectionString] String

Where ConfigureFilter is what SSIS uses to know which row to pick from the table; ConfiguredValue is the value to be used when overwriting the property value; PAckagePath will tell SSIS which property will be override.

In your case that property might be the connection string of a connection manager. Then all other objects/tasks using that connection manager, like a lookup component, should pick up the configuration value from the table.

Does this make sense?

|||

thanks phill...i was able to view his page though...

Let me explain my situation clearly...

here goes the process...

I have a package which has two task

1. execute sql

2. data flow

With in data flow..

i have a flat file connection to my text file

i update or insert records to my destination.

i have lookups,oledb commands,oledb destinations...

so far i am using oledb command...

when i insert or update what i do is insert few fields of incoming record in one table get the identity field number and insert rest of the data and the identity field to another table...which uses connection1

now then i take identity fields of the records inserted from above two tables and insert them to another table in a different database with different connection 2...

all these are in the demo environment...

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

|||

sureshv wrote:

Now my question is i want to using the same package to run but one change which i want to make is change the connection1 and connection2 to connection 3 and coneection 4.Is that possible using package configuration...

please let me know...

Yes, this is what we've been illustrating to you.

You need to chose the database connectionstring property (that's all I use) of the connection manager object you are using in your data flows. Storing the connectionstring property in the SSIS package configuration table will allow you to change the connections whenever you wish.

Note, if you use the same connection manager object in your package configuration setup as you do in your data flows, this isn't going to work -- it will never change from the server defined in the connection manager setup. You should create a separate connection manager object for the package configuration table and store its configuration in an XML config file. (Again, that's what I do.)|||

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

|||

sureshv wrote:

It am trying to get the point but it would be great with an example..

Ok one last bit of confusion here

i have configured as u suggested me but dosent seem to work...Let me make it clear on what i do...

Lets take two conections

Connection 1

Connection 2

These are used in the data flow

So in the control flow i am setting it this way...let me know if its right

Package configuration->enable package configuration->Add

Configuration type->xml file

Configuration File->file.dtsConfig

with in select properties to export

under Connection Managers->should i select connectionstring of connection1 and connection 2 or should i select connectionstring of connection 3 and Connection 4

It would be very helpfull if u let me know about it...

Thanks in advance...

You should choose the string connections you want to change (eg. from dev to prod) at run time; so if you only want to change 1 and 2; that should be your selection...

|||so in the package configuration i select connection string of connection 1 and 2...but it dosent work....it does not go to insert in the demo database instead it inserts into dev only...|||

Ok, something is not properly set up.

Just to recap; how are you implmenting package configurations? Using only an xml file? or using a SQL table-xmlfile combination?

either way; make sure the configuredValues points to the DEMO env (the connection string is correct).

Also, check the log or progress tab when running the package to see if there is any warning about package configurations not taking place....

|||

ok rafael...

it dosent seem to work at my end...

let me make myself clear...

Under control flow i have data flow task and with it i have oledb destination right now the destination tbale connected is dev environment

i have one more connetion setup which is demo environment but right now none of my tasks uses it..

Now i set up package configuration

i use xml file

i select the connetion string of the demo environment...

now i run my package...

i dont have any error or warning

the data gets stored in the development...is this the way i got to set..

Please help me out...