Post reply

SSIS Connections managers Update at Runtime

  • September 16, 2009 at 2:41 pm

    #136046

    Hello,

    I need to update the connection string (ServerName) of my OLEDB Connection. I've got on Sp that returns one output variable (ServerName). I want that the DataFlow uses the Oledb destination with the value of that variable but always is giving me one error:

    The AcquireConnection method call to the connection manager "Connection_OLEDB" failed with error code 0xC0202009.

    I've got the DFT with Delay validation, and the dataflow executes with the servername where the ssis is running. All my variables are ok, i triple checked.

    I only want to copy from one OLED Source to another OLEDB Destination but i only know my OLEDB destination on runtime.

    Thanks

  • April 30, 2010 at 5:33 am

    #1160322

    Hi.

    I've had the same issue and think I've come up with a solution.

    Before your dataflow task you need to put an execute SQL task and script task.

    Rebuild your SP to make the whole connection string in the format

    Data Source=<server_name>;User ID=<userid>;Pwd=<PW>;Initial Catalog=<Initial Catalog value>;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

    All of these details can be found under the connection properties window within SSIS.

    You will need 2 variable for this. 1 as a read only which is populated by your sp and the other a read/write variable. this second one is just to check the string for development'

    Next, within the script task you need something along the lines of.

    Public Sub Main()

    '

    '

    Dts.Connections("<name of your connection>").ConnectionString = SOURCE()

    ' this is to display the connection info in msgbox only'

    Dts.Variables("CW_CONNECTION").Value = SOURCE()

    MsgBox(Dts.Variables("CW_CONNECTION").Value)

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Function SOURCE() As String

    Dim CTHH_SOURCE As String = ""

    CTHH_SOURCE = CStr(Dts.Variables("<name of your variable>").Value)

    Return CTHH_SOURCE

    End Function

    Once you have this, the connection string should auto populate from the script task.

    Hope this helps.

Viewing 2 posts - 1 through 1 (of 1 total)

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