"Package Configurations" Problem

"Package Configurations" Problem

• Olli P

  Its not actually the loaction of the Excel file's i'm having trouble with. Its specifying the start folder to enumerate objects within. In the expressions for the for each loop there is not one for folder. Am i maybe getting the wrong idea about this I can loop through the files without a problem when hardcoding the folder value. I want this as a variable if at all possible.
  
  The link you posted seems to talk about setting the connectionstring for the connection manager.
  
  Does that make sense
  
  Thanks,
  
  Grant
  
  

• Ben Santiago

  Grant Swan wrote:

  TGnat: Thanks for this, sounds like not a bad idea about encrypting the connectionstring in the config file. I'll look into this as well. As it is i found the problem with the code. It seems that i forgot about the folder location that i was looping through in the foreach loop. I wanted to change this depending on a variable but from what i can see it won't let me. Is this a limitation in SSIS and if it is are there any available workarounds that will let me set this at runtime can Package Configurations be used for this at all It seems like something that would be used across a wide range of applications. Cheers, Grant

  No there is no limitation - you can do this. You need to set the folder location using an expression. See here for details: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

  -Jamie

  
  
  

• Gambit7

  Same again. SSIS will not let you get at the passwords. its considered a security breach.

  It is indeed a steep learning curve. i couldn't agree more Its worth it when you reach the summit though!

  -Jamie

  
  
  

• Tom Rixom

  Hi, thanks for the quick response and apologies for the lack of informtion.
  
  below is the config file exported from VS 2005:
  
  < xml version="1.0" ><DTSConfiguration><DTSConfigurationHeading>
  <DTSConfigurationFileInfo GeneratedBy="gswan" GeneratedFromPackageName="Routecard File Processor" GeneratedFromPackageID="{76B4E69C-3E96-46A1-92C9-F051AB372475}" GeneratedDate="30/08/2006 13:45:33"/>
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[ConnectionString]" ValueType="String">
  <ConfiguredValue>Data Source=(local);User ID=SQLUSER;Initial Catalog=InternalProductionData;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>InternalProductionData</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[Password]" ValueType="String">
  <ConfiguredValue></ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[ProtectionLevel]" ValueType="Int32">
  <ConfiguredValue>1</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[RetainSameConnection]" ValueType="Boolean">
  <ConfiguredValue>0</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[ServerName]" ValueType="String">
  <ConfiguredValue>(local)</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[InternalProductionData Connection Manager].Properties[UserName]" ValueType="String">
  <ConfiguredValue>SQLUSER</ConfiguredValue>
  </Configuration>
  </DTSConfiguration>
  
  
  The password field doesn't seem to have a value assigned to it yet i have set this up.
  
  Hope this provides more data to go on.
  
  Grant
  
  

• MikeyNero

  yeah, I thought that might be the problem. SSIS will not store the password for you as that is considered to be a security breach/risk/whatever. They want to make sure that storing of a password in clear text is something that YOU do - and hence you are responsible.

  You will notice that the password is not stored in the ConnectionString property either.

  The fix is easy - just modify the configuration file - add the password in yourself.

  -Jamie

  P.S. There is no need to store the ConnectionString property AND all of the other properties. Just storing ConnectionString will suffice.

  
  
  

• Ron L

  Same again. SSIS will not let you get at the passwords. its considered a security breach.

  It is indeed a steep learning curve. i couldn't agree more Its worth it when you reach the summit though!

  -Jamie

  
  
  

• .Adrian

  Grant Swan wrote:

  TGnat: Thanks for this, sounds like not a bad idea about encrypting the connectionstring in the config file. I'll look into this as well. As it is i found the problem with the code. It seems that i forgot about the folder location that i was looping through in the foreach loop. I wanted to change this depending on a variable but from what i can see it won't let me. Is this a limitation in SSIS and if it is are there any available workarounds that will let me set this at runtime can Package Configurations be used for this at all It seems like something that would be used across a wide range of applications. Cheers, Grant

  No there is no limitation - you can do this. You need to set the folder location using an expression. See here for details: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

  -Jamie

  
  
  

• AlexCr

  Grant Swan wrote:

  TGnat: Thanks for this, sounds like not a bad idea about encrypting the connectionstring in the config file. I'll look into this as well. As it is i found the problem with the code. It seems that i forgot about the folder location that i was looping through in the foreach loop. I wanted to change this depending on a variable but from what i can see it won't let me. Is this a limitation in SSIS and if it is are there any available workarounds that will let me set this at runtime can Package Configurations be used for this at all It seems like something that would be used across a wide range of applications. Cheers, Grant

  No there is no limitation - you can do this. You need to set the folder location using an expression. See here for details: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

  -Jamie

  
  
  

• Joe Pickering

  Thanks for that,
  
  While it ceratinly has resolved the issue i have it i'm now slightly concerned over the security issues that a plain text password entails. Is there a better way to do this type of thing
  
  Is it likely to be of benefit if i set up a database with the Integration Services configurations stored in it. Would this be more secure
  
  Any advice on best practice in these instances would be of great help to me.
  
  Thanks,
  
  Grant
  
  

• hye_heena

  You haven't told us what properties you are storing in the configuration file.

  If its not too big, how about posting the contents of the configuration file up here (Don't paste from IE. Open up the file in a text editor or preferably Visual Studio and copy/paste from there)

  -Jamie

  
  
  

• Steve Hittle

  TGnat: Thanks for this, sounds like not a bad idea about encrypting the connectionstring in the config file. I'll look into this as well.
  
  As it is i found the problem with the code. It seems that i forgot about the folder location that i was looping through in the foreach loop. I wanted to change this depending on a variable but from what i can see it won't let me. Is this a limitation in SSIS and if it is are there any available workarounds that will let me set this at runtime can Package Configurations be used for this at all
  It seems like something that would be used across a wide range of applications.
  
  Cheers,
  
  Grant
  
  
  

• Reza Bemanian

  Grant Swan wrote:

  Thanks for that, While it ceratinly has resolved the issue i have it i'm now slightly concerned over the security issues that a plain text password entails. Is there a better way to do this type of thing Is it likely to be of benefit if i set up a database with the Integration Services configurations stored in it. Would this be more secure Any advice on best practice in these instances would be of great help to me. Thanks, Grant

  Yes that would be more secure you can restrict access to them and you can also encrypt them as well. Other options:

  1. If you are storing passwords in a configuration file then you can limit access to the folder that they are stored in.
  2. You could use Windows Authentication rather than SQL Server authentication.
  3. You can store passwords within the package in an encrypted format (explore the PackageProtection property)
  4. You can store passwords in the registry and limit access to it.

  The only option not open to you currently is to encrypt the configuration files. SSIS does not support this (yet).

  Not sure what "best practice" is currently. I prefer to use configuration files so option 1 (above) would be the way I would go! But that's just me.

  HTH

  -Jamie

  
  
  

• lex_o

  Appreciate the help. This is all really helpful stuff. I'll look into each of the methods to see what is best suited although the config file is probably best as it's going to be help on an internal SQL server.
  
  Not wanting to push my luck with all the questions i wonder if you could answer one more thing. I'm trying to call the package from an c# .net application. I've set up the following code to test it:
  
  [code]
  //processDownloadedFolders();
  Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
  Package sqlDts = app.LoadFromSqlServer("Routecard File Processor", <servername>, <username>, <password>, null);
  
  //Set up the initial variables for baseArchivePath, baseQuarantinePath, baseRoutecardDirectory, BESProcessingFolder
  Variables var = sqlDts.Variables;
  var["baseArchivePath"].Value = tbRoutecardArchiveFolder.Text.ToString();
  var["baseQuarantinePath"].Value = tbRoutecardQuarantineFolder.Text.ToString();
  var["baseRouteCardDirectory"].Value = tbDownloadPath.Text.ToString();
  var["BESProcessingFolder"].Value = tbCSVDestination.Text.ToString();
  
  sqlDts.ImportConfigurationFile(@"C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Routecard File Processor\routecard file processor configuration settings.dtsconfig");
  
  sqlDts.ImportConfigurationFile(@"C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Routecard File Processor\routecard file processor configuration settings.dtsconfig");
  
  DTSExecResult dtsResult = sqlDts.Execute();
  
  MessageBox.Show(dtsResult.ToString());
  [/code]
  
  For some reason when i do some checks to see what the connection string paths are they always return it without the password section in it. Even loading the config file doesn't make a difference. It then appears to run very quickly and does nothing. The return result is however a success.
  
  Aorry for all the questions, i'm new to integration services and its a steep learning curve.
  
  Cheers,
  
  Grant
  
  

• Whoisit

  For what it's worth...

  I run my packages through a .Net application. The packages access their connection strings through variables. The .Net application passes values to the connection string variables at run time. With that set up I can store the connection string in the .Net application's config file as encrypted text. The application decrypts the connection string before passing it on to the SSIS package...

  
  
• "Package Configurations" Problem