How to convert Dynamic Properties to SSIS Package Config?

How to convert Dynamic Properties to SSIS Package Config?

• Citidel001

  XML config file is the most analogous thing to the Dynamic Properties Task. Whatever you do you're going to have to type in the 1000 passwords so that isn't a consideration. Is it

  -Jamie

  
  
  

• Michael Miller

  No they are 10 connections to the same system. We use at least 4 connections so that we have them all running at the same time. What we are doing is trying to test using one connection and seeing if it replicates itself into 4 connections. Our main goal is multiple pipes running at once. In DTS this required 4 connections... Im starting to belive that in SSIS, its one connection manager, but will make multiple connections in the data flow based on that manager

  
  

• basincreek

  You "use" the configuration file by right-clciking on the package and choosing configurations, then mapping the configuration value to the property of the package you would like to configure (like a connection string).

  For your example, a configuration file by itself will not solve everything. A couple of options:

  One, you could create a seperate configuration file for each connection. Execute the package once for each source database, using a different configuation file each time.

  Two, you could store your connect strings in a table, and read them into a resultset using an Execute SQL task. Set your connection manager to get the connection string from a variable using expressions. Then use a For Each container to store the connect string in the variable. Put your data flow to move the data from the source to the destination inside the For Each container. The For Each container will loop through the list of connection strings, executing the data flow for each one.

  
  
  

• pauldo

  If you have 3 lines in your ini file, that implies three connections, not thousands, so in SSIS you would have a configuration file or table with three entries, one for each of the three connections. Where do the thousands come from It may be better to put each connection into it's own file and apply the configuration to each package that needs it. You can have more than one configuration for each package, but what you cannot do is have settings in a configuration that cannot be applied, so if a package only wants two out of three entries it is a problem. It depends on the coverage of connections across packages.

  
  
  

• ChristopherSeewald

  You posted this quite a while ago but I'm having issues with using a config table current to the server it's running the SSIS package on. Obviously, each server has a different value in the config table but for some reason, no matter what server the package is running on, it's still using the default server value. Did you come across this problem

  Thanks!

  
  

• JasonInTacoma

  No we have 2 lines one for username and one for the password. We use these within a dynamic property and set all our connections from this one database with this 1 ini file. It sets all the connections to the same username and password. The password changes to often so we only have to change it in one place. With the way SSIS is we would have to change way too many lines and i'm trying to get around that. The person updating this file does not have the time to go through this many rows. With the database I can script out a insert statements when new connections are added. I just wish they would have left this alone. We have 30 packages to convert in 3 days and this issues has taken up about a day already.

  Thanks for the help guys.

  Jerry

  
  

• Steve Maine

  Jamie,

  That is the part that we were missing in our understanding of the connection manager. We thought it was identical to the DTS Connections. Now that we know this we will be using one connection manager for all packages so we can use 1 xml file with the one connection that includes the user name and password. I really appreciate all of your and Darren’s help.

  Thanks

  Jerry

  

  
  

• Paulo X

  How many unique connections do you have By rights you should only have one, resuing passwords, not good practice :)

  Generating an XMl document is not hard, you would just need a list of your connection names Still if you prefer to use a table as your configuration location, then this is perfectly valid as well.

  
  
  

• darom

  Hi Jerry,

  Can I clarify one thing.

  Your 10 connections that exist in the same package, they are all pointing at 10 completely different systems. Albeit 10 different systems that have the same username and password.

  Is that correct

  -Jamie

  
  
  

• Reghunath

  Im not sure Im explaining what we had vs what we have to do now.

  Old way

  config.ini

  pw=password

  username = user

  30 packagages

  Each with 1-10 connections to the DB that uses the User/Pw combo from ini file.

  Each connetion would use dynamic prop to set the pw/user to the SINGLE entry in the ini file.

  New Way

  config.xml (or sql table)

  One Entry PER connection PER package = ~100 IDENTICAL Value entries, just with different package paths (IE: \Package.Connections[CON 1].Properties[Password]).

  I see that I can reuse the XML file IF the connections are named the same, this is fine, so I can name all my connections the same across packages (CON 1, CON 2 etc). The problem with this is so my package that has the most tasks in it has 10 connections (CON 1- CON 10). Once all 10 entries (with identical values) are in the XML file, the big package loads fine. But when I load one of the smaller packages that only has CON 1, then it will fail becuase CON 2-CON 10 don't exist in it (so the name has to be the same AND they have to have the same amount of connections or the package fails with the config file, same for the SQL table if using same filter).

  So I need to make multiple config files now with multiple entries containing the same Value (pw and user).

  I hope this clears up what we use to have vs what we seem to have to do now, one entry with a value that could be used across all connections/packages, vs one entry with the same value PER connection/package.

  We are looking for any way where we can get back to using ONE entry (in any solution, be it INI, XML, SQL ).

  We are re-writing the packages, rather than converting them, so we are open to what ever we seem to be missing. Someone mentioned you should have only one unique connection, in the old DTS we would have 4 or more, so that it could have 4 items running at once, not in sequence, is there some other way where a single connection can run multiple pipes in SSIS that we havent figured out yet Or any other solutions/ideas.

  Thanks!

  
  

• Polita

  There is no limit to what you can put into an XML configuration file. if you have 1000 connections then you can put the connection string in there for each one of them.

  -Jamie

  
  
  

• Jimqjp

  Hi Jerry,

  SSIS Connection Managers are not the same as DTS Connections (hence the different name).

  You are right that in DTS multiple Connection objects were required because each Connection object maintained a single connection to the data source. Not so in SSIS. The SSIS OLE DB Connection Manager maintains a pool of connections, it does NOT maintain only one connection that is shared by each task that is using it.

  Hence, in your situation I'm pretty sure you only need one connection manager. Change it to use just one connection manager and it should be OK.

  -Jamie

  P.S. You can make a SSIS Connection Manager exhibit the same behaviour as a DTS Connection by setting RetainSameConnection=TRUE. This property is FALSE by default.

  
  
  

• Dan-Teklynx

  I believe i understand how to create the config file, but what i don't understand is how to "use" it in the SSIS package. Do i add an XML source How do i access these properties from the config file. I have several databases that have the same table that i want to cycle thru the databases and put the data from the same table into 1 table on another database. For instance db1.tablea, db2,tablea,db3.tablea INTO db4.tabled. Any help would be greatly appreciated.

  Thanks!

  
  

• Peter Wester

  Jamie,

  What we did in the past was have 1 file that covered every connection. I was trying to mimic that some process with SSIS. It seems that it isn't going to happen. I think i'm going to go with the database table and use a different filter for each package. Do you have any other ideas on how to do this with 1 file

  Thank You for you reply

  
  
• How to convert Dynamic Properties to SSIS Package Config?