locked
one package running with different connection strings? RRS feed

  • Question

  • I have a SSIS package which needs to connect to 5 different servers to import data. I want to schedule one task to run this one .dtsx  but run once for each server connection( 5 different servers). 

    Is it possible? how can i accomplish it? Is Package configuration possible? How to go about it if so?

    thanks,

    kushpaw

     

    Thursday, October 5, 2006 12:58 AM

Answers

  • So, are you planning to run the same package n times but connecting to a different data source each time?

    On a previous project I accomplished that by creating a parent package with multiple sequence containers in it; in your case it would have 5. Each sequence container had 2 things

    • a variable, e.g. @User::SourceConnectionString, that hold the proper connection string for each data source. In your case it would be 5 variables with the 5 connection strings. 
    • A execute package task pointing to the package that would perform the import.

    Then in the child package you define a package configuration to set the connection string of your connection manager from the 'Parent package Variable'   @User::SourceConnectionString. At the end you just need to run your parent package and hopefully at run time the child package will be executed n times using a different connection string each time.

    In my case, the package had to extract data from a table that existed in 8 different Dbs/Servers but the table structure was exactly the same. So, I am assuming this is your case.

    I could have used a forEachLoop container with a single Execute Package task in the parent package, but it would not allow me to run the packages in parallel which was another of my goals.

    In the parent package, if you are concern about having your connection strings exposed inside of variables; you could define a package configuration to set the value of the variables at run time from a table, xml file, etc. That was what I did.

    I hope this give you some ideas…

    Rafael Salas

     

     

    Thursday, October 5, 2006 1:06 PM

All replies

  • So, are you planning to run the same package n times but connecting to a different data source each time?

    On a previous project I accomplished that by creating a parent package with multiple sequence containers in it; in your case it would have 5. Each sequence container had 2 things

    • a variable, e.g. @User::SourceConnectionString, that hold the proper connection string for each data source. In your case it would be 5 variables with the 5 connection strings. 
    • A execute package task pointing to the package that would perform the import.

    Then in the child package you define a package configuration to set the connection string of your connection manager from the 'Parent package Variable'   @User::SourceConnectionString. At the end you just need to run your parent package and hopefully at run time the child package will be executed n times using a different connection string each time.

    In my case, the package had to extract data from a table that existed in 8 different Dbs/Servers but the table structure was exactly the same. So, I am assuming this is your case.

    I could have used a forEachLoop container with a single Execute Package task in the parent package, but it would not allow me to run the packages in parallel which was another of my goals.

    In the parent package, if you are concern about having your connection strings exposed inside of variables; you could define a package configuration to set the value of the variables at run time from a table, xml file, etc. That was what I did.

    I hope this give you some ideas…

    Rafael Salas

     

     

    Thursday, October 5, 2006 1:06 PM
  • Just a thought.  I have not tried this but it should work in theory.

    You can store your connection strings in a DB, query the table and do a for each container to loop through them in the control flow.  Before going to your Data Flow, use the script task to assign the value to your connection variable.  In the properties of your data source you can set an expression to assign the variable as the connection string.

    This would make your package much cleaner looking and make adding additional connections easier by adding a row to your table.

    Thursday, October 5, 2006 4:09 PM
  • Rafeal,  this is exactly what i am doing. I am connection to AS400's different libraries where table structure is same. Did you have any issue during deployment or testing. Can you suggest any link where i can follow some steps. I have never used sequence container.
    Thursday, October 12, 2006 1:24 AM
  • Rafael,  how did you implement error handling and logging. If one child package failed? I my case all data flow are independent, next should process on completion of previous.
    Thursday, October 12, 2006 1:34 AM
  • Kushpaw,


    Rafeal,  this is exactly what i am doing. I am connection to AS400's different libraries where table structure is same. Did you have any issue during deployment or testing. Can you suggest any link where i can follow some steps. I have never used sequence container.

    I implemented this to extract 10 tables times 4 databases; I had some issues with the biggest table when running the 4 packages that seemed to be related to a timeout  issue in my data reader component; so I had to increment the commandTimeout parameter and that seemed to fix the problem. But let me clarify; on that project the 4 DBs were in the same server and the DB engine was a old Pervasive version with only ODBC drivers available. You have to test yourself to see if that is going to be an issue for you. I could not find any link or other information about this topic; so got this through ‘trial and error’ method; so my advise is test, test and test.

    Rafael,  how did you implement error handling and logging. If one child package failed? I my case all data flow are independent, next should process on completion of previous.

    Logging and error handelling...good point. Since you are running the same package in parallel you have to be cautious when looking at the logging tables; in this case the package ID will no be enough to determine where a problem/action took place; SSIS packages have an system variable that will give you the execution instance as well. In my case I had custom execution log tables; I used the package name in combination with package a variable that I called PackageExecutionInstance which I populated via expression with enough information to distinguish the package execution (for me the SourceDatabase name).

    I hope this answer your questions and please come back with your final approach so we all learn ;).

    To BMcDowell comment;
    I agree with you; using a ForEach loop conatiner may be cleaner and easier to mantain; but as I said in my previous post it does not provide the ability of running in parallel.

     

    Rafael Salas

    Thursday, October 12, 2006 2:23 PM