Post reply

script task and configuring to different environment

  • November 25, 2009 at 8:21 am

    #214253

    i have written a package in script task using vb .net and it running fine

    i have done the connection string hardcoded in the vb .net

    DBA are telling they can't move different package to different environment, i should control with the configuration file the different environment like dev,uat ,prod and contingency.

    please advise how to go about it as i have done this before.

  • November 25, 2009 at 10:00 am

    #1084189

    [font="Comic Sans MS"]Hello - here you go :

    http://www.lmgtfy.com/?q=SSIS+Easy+Package+Configuration

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • November 25, 2009 at 10:44 am

    #1084220

    Thanks that link takes me to google.

    i have done configuration using xml file for data flow task and other controls.

    i want to know how to do it inside script task that is where i needed help

  • November 25, 2009 at 11:35 am

    #1084242

    The likely reason the DBA does not want to move it is due to hard-coded connection managers to the dev environment. Due to that the DBA is recommending the config file. Alternatives to the config file would be to use a database table to control the connection. This kind of setup requires additional planning in order to avoid the need to recompile the package prior to deployment to prod.

    Another alternative is to pass a variable into the package at job run time that overrides the connstring being used.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 25, 2009 at 12:21 pm

    #1084271

    [font="Comic Sans MS"]

    Hello - if you have used configuration through xml already - I assume you know how to put the database details in the xml configuration file.

    Assume your database connection name is : ABC_DB_Connection (which is mapped appropriately to configuration file)

    Now:

    Step 1: Go to the connection manager tab of script component/transformation editor.

    Step 2 :Put the connection manager [ABC_DB_Connection] and name [ say ABCDb] there.

    Step 3: Inside script use System.Data.OleDb

    Step 4 : Use the below code ..

    'Define and open the database connection

    .Connection = New OleDbConnection(Connections.ABCDb.ConnectionString)

    .Connection.Open()

    Try

    'Execute the procedure or command here

    Finally 'Always finalize expensive objects

    .Connection.Close()

    .Connection.Dispose()

    End Try

    And you are ready to go ...

    Let me know if you need anymore help ..

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • November 25, 2009 at 1:27 pm

    #1084320

    thanks for the information

    Can you please elaborate how to pass a variable and overwrite at run time?

  • November 25, 2009 at 1:33 pm

    #1084327

    keywestfl9 (11/25/2009)

    thanks for the information

    Can you please elaborate how to pass a variable and overwrite at run time?

    [font="Comic Sans MS"]

    Please refer to BOL for this - I think you would need a better understanding on script component. BOL has many excellent example of script task/transform.

    For a quicky you may refer to Jamie's blog (it's a lifesaver for me):

    http://consultingblogs.emc.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply