package configurations

Hi,

i keep struggling with dtsconfig.

i have 4 packages which all have the same datasource based on the global-datasource.

is there a way i can save that global-datasource, instead of configuring all 4 packages to use a config-file?

Mafti,

What is your issue?

Setting package configuration should be a 1 time setting during development; then you have to change the config file only when a change in the connection string occurs (server name, credentials, etc).

The other way would be to save the connection credentials (some sensitive data involved) within the package definition; but for that SSIS requires the sensitive data to be encrypted; creating other potential deployment issues. If you want to follow that path; look in BOL or this forum for a property at the package level called ProtectionLevel

|||

I think I have the same question.

I want to set my connection strings from a config file.

Is there some way to configure the Global Datasource to use an expression to set the connection string? It seems the only way to set it from a config file is to add a connection to the connection managers from the Global Datasource, then set the expression for the connection in the package itself. Thereby making the use of the Global Datasource useless in this instance, other than not having to retype the name.

|||Make it simple:

We have 1 global .dtsConfig file which stores db connection string (all packages have variable DB_CONN_STR) - and each DB provider you use - use expression to set the connstring

Also - for every package we have separate config file to set specific info like FTP user/pass etc.|||

that is what i am doing.

the problem is that there is no way to set up a global datasource to use a variable, or so it appears.

That forces you to either:

1) create a new connection each time and configure it to set the connection string from a variable (expression)

2) use a global datasource then modify the actual connection created from it to set the connection string from a variable (expression).

|||

Karstman wrote:

that is what i am doing.

the problem is that there is no way to set up a global datasource to use a variable, or so it appears.

That forces you to either:

1) create a new connection each time and configure it to set the connection string from a variable (expression)

2) use a global datasource then modify the actual connection created from it to set the connection string from a variable (expression).

No matter if you use a 'global data source'; the data source information is copied on each package that uses it. Thos solution for me is not to use those data sources at the project level as I don't see any added value on using them.

package configurations

Hi,

i keep struggling with dtsconfig.

i have 4 packages which all have the same datasource based on the global-datasource.

is there a way i can save that global-datasource, instead of configuring all 4 packages to use a config-file?

Mafti,

What is your issue?

Setting package configuration should be a 1 time setting during development; then you have to change the config file only when a change in the connection string occurs (server name, credentials, etc).

The other way would be to save the connection credentials (some sensitive data involved) within the package definition; but for that SSIS requires the sensitive data to be encrypted; creating other potential deployment issues. If you want to follow that path; look in BOL or this forum for a property at the package level called ProtectionLevel

|||

I think I have the same question.

I want to set my connection strings from a config file.

Is there some way to configure the Global Datasource to use an expression to set the connection string? It seems the only way to set it from a config file is to add a connection to the connection managers from the Global Datasource, then set the expression for the connection in the package itself. Thereby making the use of the Global Datasource useless in this instance, other than not having to retype the name.

|||Make it simple:

We have 1 global .dtsConfig file which stores db connection string (all packages have variable DB_CONN_STR) - and each DB provider you use - use expression to set the connstring

Also - for every package we have separate config file to set specific info like FTP user/pass etc.|||

that is what i am doing.

the problem is that there is no way to set up a global datasource to use a variable, or so it appears.

That forces you to either:

1) create a new connection each time and configure it to set the connection string from a variable (expression)

2) use a global datasource then modify the actual connection created from it to set the connection string from a variable (expression).

|||

Karstman wrote:

that is what i am doing.

the problem is that there is no way to set up a global datasource to use a variable, or so it appears.

That forces you to either:

1) create a new connection each time and configure it to set the connection string from a variable (expression)

2) use a global datasource then modify the actual connection created from it to set the connection string from a variable (expression).

No matter if you use a 'global data source'; the data source information is copied on each package that uses it. Thos solution for me is not to use those data sources at the project level as I don't see any added value on using them.

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 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 - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Package Configuration

I have created a package configuration to read the connectionstring for the oledb connection manager.

Saved the file as Environment.dtsConfig in the same directory as the other packages.

Do the packages refer to the configuration file automatically?

Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?

Thanks

Yes and no.

You can check what Configurations are "bound" to a package, open the package and then go to the Configurations menu option in the designer. The first dialog shows you all configurations that will be applied to that package.

There is no difference in the way you use DTEXEC now that the package has a configuration.

Package configuration

Hi all of us,

I'm looking any .dtsconfig file as sample for my first package configuration. I've got ssis packages which might be updated on development and then moved to production. First of all, I'd like dynamically change the connections but I don't know exactly how to do that. I'm taking a look on the wizard but when I choose a Sql Server request me a table.

Any sample or link for that would be welcomed!

TIA

Ok, I begin to understand something...

<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="REDCAM\AdminSql" GeneratedFromPackageName="TestMan_PreProduccionCargaBuildPaso" GeneratedFromPackageID="{2E73D76E-4AAF-4BEF-BDE6-379C90193FB1}" GeneratedDate="10/17/2006 1:10:05 PM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ExcelFilePath]" ValueType="String">

<ConfiguredValue>\\srvaa8\Ficheros\ssis\BuildPasos.xls</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::RutaFicheroCarga].Properties[Value]" ValueType="String">

<ConfiguredValue>\\srvaa8\Ficheros\ssis\</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Variables[User::RutaFicheroError].Properties[Value]" ValueType="String">

<ConfiguredValue>\\srvaa8\ficheros\TestMan\Produccion\SinDatosPrevios\</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Properties[VersionComments]" ValueType="String">

<ConfiguredValue />

</Configuration>

</DTSConfiguration>

My question is how to set different values to my file? How to tell the .dtsx that uses this file or not?

TIA again,

|||

Ok, I'm sorry for this mess of the post, I'm awaring of things as time goes by...

I've seen that is possible do the same using a table but my wonder now is the following: is it possible use the same table for more than one package? I guess not but...

Thanks again for your patience,