0

I am building a SSIS package using BIDS 2008. I have configured the package read the configuration settings in the database.

For e.g I have the following connectionstring in my package

<DTS:Property DTS:Name="ConnectionString">Data Source=xxxxx\SQL2008R2;User ID=;Initial Catalog=xxxxx;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=xxxxxL-{452DA0BD-2ACF-4780-9DB0-5A64ABB406A1}xxxxxx.xxxxxx;Auto Translate=False;</DTS:Property>

The configuration setting in the database is as follows enter image description here

I need to be able to deploy the packages in all environments without changing the connectionstring in the package itself. I tried to modify the connectionstring in the package thinking that it will read the connectionstring from the configuration settings in the database.It unfortunately didn't work. So what is the use of the configuration settings if I have to modify the package file for every environment.

Currently it only works if the connection string in right in the package as well the database

Tom
  • 8,175
  • 41
  • 136
  • 267
  • How have you "configured the package read the configuration settings in the database"? – Tab Alleman Aug 23 '17 at 12:41
  • Not sure what needs to be done. I was under the impression it happens in the background and is not explicit – Tom Aug 23 '17 at 12:49
  • 1
    No, package configurations must be explicitly specified. I suggest googling `SSIS Package Configuration tutorial` and learning the basics. – Tab Alleman Aug 23 '17 at 12:54
  • I have gone through the tutorial but little confused. The tutorial tells me to create a SQL Server configuration type using Package Configuration Wizard. If I empty the connection strings from dtx and run it, it works. My only problem here is that I need to select the connection string which which will access the configuration table. How will I tell the package that the database server name is going to be different for every enviornment in this configuration. In my scenarios, The connection string is going to be different for each enviornment – Tom Aug 24 '17 at 14:13
  • You will put your connection strings in a .dtsconfig file, and deploy a different version of the file to each environment. The file you deploy to DEV will have the connection strings to the DEV database, etc. – Tab Alleman Aug 24 '17 at 14:29
  • Hi Tab, I am doing a database based deployment and not File based. So my package as well as configurations are stored in the database – Tom Aug 24 '17 at 14:38
  • Do you have a link to some article explaining how your configurations can be stored in the database? I don't think this is an option in SSIS 2008. That was the point of the question in my first comment by the way. While you're at it, see if this helps: https://stackoverflow.com/questions/14652931/ssis-configuration-connection-strings?rq=1 – Tab Alleman Aug 24 '17 at 14:59
  • I am not sure about the configurations being stored but know that package can be stored. Here is the link. I have managed to create configurations table in Sql Server 2008 server and my package seems reading the configurations. Please check the link http://www.c-sharpcorner.com/blogs/deploying-ssis-packages-of-bids-2008-into-sql-server-2008 – Tom Aug 24 '17 at 15:16

1 Answers1

0

You seem to have a basic misunderstanding of how configurations can work in SSIS 2008.

A "configuration table" can be used to hold all of the environment based information your package needs EXCEPT the connection string. If you think about it, it's pretty obvious. How can your package possibly get the connection string from a table in the database, without having the connection string to that database in the first place?

The connection strings for your package MUST be kept in a .dtsconfig FILE. This is true NO MATTER whether your package is going to be in the file system or in the database on your SSIS Server. Also no matter what deployment method you use. The ONLY way to have different connection strings in different environments is to store them in a .dtsconfig file, and deploy different versions of that file to different environments. The file you deploy to your DEV environment will have the connection strings to DEV, etc.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Is this applicable even in SSIS 2016 – Tom Aug 27 '17 at 15:21
  • I don't know. Your question says you are using 2008. – Tab Alleman Aug 28 '17 at 12:56
  • Yes there is a possibility I may migrate to ssis 2016 to leverage better deployment options – Tom Aug 28 '17 at 22:19
  • That would then become a different question. I don't know the answer for 2016. But again I ask, How can your package possibly get the connection string from a table in the database, without having the connection string to that database in the first place? – Tab Alleman Aug 29 '17 at 12:43