none
How to add a connection manager at runtime within a script component?

    Question

  • Hi all. Sorry about bothering you again but I have no choice, I'm afraid. This time I have to add a connection manager at runtime. I have a script that examines rows in a huuuuuuuuuuuuge file and it turns out that I will have to redirect different rows into 95 different text files. I would like to avoid the pain of creating the files and connection managers manually. I have a source script component that runs asynchronously (the only way basically it can run as a source) and I wonder whether this time, when I change the connction string on the manager at runtime inside the script, it would run properly. Can anybode tell me, please?

     

    Darek

    Monday, October 29, 2007 5:39 PM

Answers

  • You cannot change a package at runtime, other than through -

    • Configurations
    • Variables
    • Proprty expressions

     

    You cannot even set a property from within a script task. This was a conscious decision by the team when designing SSIS.

     

    Maybe you would be better looking at a package generator rather than trying to build a self-generating package? A subtle shift in mindset my help.

    Monday, October 29, 2007 11:28 PM
    Moderator

All replies

  • You cannot change a package at runtime, other than through -

    • Configurations
    • Variables
    • Proprty expressions

     

    You cannot even set a property from within a script task. This was a conscious decision by the team when designing SSIS.

     

    Maybe you would be better looking at a package generator rather than trying to build a self-generating package? A subtle shift in mindset my help.

    Monday, October 29, 2007 11:28 PM
    Moderator
  • This still seems like a script destination to me, if you even want to use SSIS at all. I do think that SSIS offers some benefits over a C# or VB.NET application for these types of things, particularly the logging and connection management.

     

    Tuesday, October 30, 2007 1:15 AM
    Moderator
  • hi.

    i think its possible by changing the dtsconfig file and we can select the connection manager.

     

    all the we need to do is, we have to create a xml configuration file (somename.dtsconfig)

    we have to build our package, later, if you want to modify the connection manager, we can do the modification  in that dtsconfig file, it will affect the package execution.

     

    i tried this, its working for flat file source; it might work for oledb connections also. i hope so!!

     

    please do reply if its working with oledb connection.

     

    thanks a lot

     

    Tuesday, December 11, 2007 7:38 AM
  • Configurations can change any property value. They will work just as well against an OLE-DB connection.

     

    (To be clear this is different to the original question, as here you are changing property values in advance through the configuration mechanism, not trying to add a new object. The structure of the package does not change.)

    Tuesday, December 11, 2007 11:24 AM
    Moderator
  • hi,

    as you mention the above, i try modifying the connnection string and its properties in the dtsconfig file. but its not affecting the package execution. its working fine for flat file source. if we are changing the connection properties of the oledb connection, its not getting changed. its showing new values only in the dtsconfig file. not in the package execution.

     

    by going with environment variable. we can give a path in that env variable. and we cannot specify the properties of the configurations. its adding all the available configurations details in my project and loading into the specified location.

     

    i'm working on it. and let you know the status. please guide me if i'm going in some wrong path.

     

    Thanks a lot.

    Tuesday, December 11, 2007 12:47 PM