ssis looping through connections

  • elaine-131617

    Ten Centuries

    Points: 1066

    Can anyone please tell me how to loop through the connections in SSIS? In DTS I used to loop through the connections for output files in order to rename them with the current date added to the connection name. I used to set DTSGlobalVariables.Parent.Connections.Item(i).Name for each item in DTSGlobalVariables.Parent.Connections, but I don't know how to do it in SSIS. I've been Googling and looking through other posts in this forum, but I haven't seen anything that addresses this issue.

    Thanks

    Elaine

  • Ben Sullins-437405

    SSCertifiable

    Points: 5397

    Are you asking how to dynamically set the connection string or how to loop through a directory of files?


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • elaine-131617

    Ten Centuries

    Points: 1066

    There are about 12 different flatfile connections in addition to an odbc connection. I would like to rename each text file when the package is run - for example, adding the current date to the output file name. I want to know how to loop through all the connections in the connection manager to rename them when the package runs. I used to do this with DTSGlobalVariables.Parent.Connections in dts, but I don't know what to use in ssis.

    Elaine

  • pduplessis-723389

    SSChampion

    Points: 10599

    Use a for each ADO enumerator.

    Now you can write a query to get your connection strings, and pass this to an object variable.

    Good luck

    ~PD

  • elaine-131617

    Ten Centuries

    Points: 1066

    Thank you!

  • ruizeddy

    SSC Veteran

    Points: 293

    Can you elaborate on how to do this please? I have a similar issue, and I just recently started using SSIS.

    May your fish always be bigger
    Than the holes on your net.

  • elaine-131617

    Ten Centuries

    Points: 1066

    There is probably a more elegant way to do this, but this is what I ended up using, and it works for my report. Inside the script task I do this:

    dim filepath as string

    dim i as integer

    filepath = an output path variable that I had passed into the ssis package with a date string that is part of the naming convention of the output files

    For i = 0 To Dts.Connections.Count - 1

    If Dts.Connections.Item(i).ConnectionString.Contains(" some common string such as part of the directory for the connections in the package that I want to set the name ") Then

    Dts.Connections.Item(i).ConnectionString = filepath & Dts.Connections.Item(i).Name & ".txt"

    End If

    Next

  • ruizeddy

    SSC Veteran

    Points: 293

    Thanks!

    May your fish always be bigger
    Than the holes on your net.

  • ruizeddy

    SSC Veteran

    Points: 293

    I wound up using:

    Dim strConnect As String

    strConnect = "Data Source=" + Dts.Variables("User::currentserver").Value.ToString + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

    Dts.Connections(" ").ConnectionString = strConnect

    I'm trying to retrieve database settings, such as recovery model (and a few others) for each database, for each SQL server and store the results in a central database, so it can be easily viewed. The server name is the first foreach loop. The databases collection is the second foreach loop. For each iteration of each loop, the server and database values are stored in global package variables. I'm able to cycle both loops successfully, I'm now stuck on the SQL task that returns the settings (recovery model) value:

    ------------------------------------------------------------------

    DECLARE @QUERY AS VARCHAR(200)

    DECLARE @OR AS VARCHAR(1000)

    declare @recmodeltbl table(recovery_model varchar(100))

    insert into @recmodeltbl select convert(varchar(100),DATABASEPROPERTYEX (@dbs , 'Recovery')) as recovery_model

    select @recovery_model = recovery_model from @recmodeltbl

    ------------------------------------------------------------------

    under Parameter Mapping for the SQL task I mapped

    Dts.Variables("User::currentserver") to @server as input

    Dts.Variables("User::currentserver") to @dbs as input

    Dts.Variables("User::recovery_model") to @recovery_model

    I get: [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

    I have tried single result set and full result set. Any ideas?

    May your fish always be bigger
    Than the holes on your net.

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

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