Dynamic Connection String for Dataflow Task

  • oogibah

    Right there with Babe

    Points: 792

    Hey Guys,

    I've been tasked with doing a task where I connect to a number of different sql servers, pull audit logs, and combine them into one table on our DBA instance.  I've seen MANY posts about this online, read through, followed the steps that I can mirror on my own here but it doesn't seem to be behaving the same as everyone elses I see posting online and I was wondering if I could get some help.

    So Step 1: I pull the data from my table of Servers/Instances from a SQL Server Table

    Step 2: I use a for each loop using Foreach ADO Enumerator and use the variable mappings to populate both a connection string variable and a server variable.  NOTE: THIS WORKS The variables get populated and change through every iteration.

    Step 3: Set the dynamic connection managers expression to use the server variable as Data Source and ConnectionString with the connection string variable this does not work.  Even though  the variables are updating, the connection string never does.  The Data source stays blank.

    Note 1: I've attempted this with connection string parameterized and with data source parameterized or with either one, none of it seems to populate in the connection manager once the package is started.

    Note 2: Initial catalog is static so there's no need to modify it.  I do have it assigned to a variable though in my testing and it does populate normally, I assume this is because its set at the start of the script.

    Based off the note above, I need to somehow force the connection string to recompile on each loop.  Any ideas on how to do this?

    EDIT: Delay validation is set to true for the package.

     

    • This topic was modified 4 weeks ago by  oogibah.
    • This topic was modified 4 weeks ago by  oogibah.
  • anthony.green

    SSC Guru

    Points: 112352

    Ok how I've done this in the past.

    Create a variable of type "Object" to store the ADO.NET connection string table

    Create a variable of type "String" to store the individual connection string

    Create an "Execute SQL Task" as to pull the fully built connection string only from the source table of what to loop over

    From this task, dump the result set to the variable of type Object

    Have a "For Each Loop" for "Foreach ADO Enumerator" source variable is the "Object" variable, "Rows in the first table"

    On the loops variable mapping, map output to the "String" variable

    On your connection manager, set the connection string expression to be the "String" variable

  • oogibah

    Right there with Babe

    Points: 792

    anthony.green wrote:

    Ok how I've done this in the past.

    Create a variable of type "Object" to store the ADO.NET connection string table

    Create a variable of type "String" to store the individual connection string

    Create an "Execute SQL Task" as to pull the fully built connection string only from the source table of what to loop over

    From this task, dump the result set to the variable of type Object

    Have a "For Each Loop" for "Foreach ADO Enumerator" source variable is the "Object" variable, "Rows in the first table"

    On the loops variable mapping, map output to the "String" variable

    On your connection manager, set the connection string expression to be the "String" variable

    I believe I have all of those things covered.. see below:

     

    CREATE TABLE [##ConnString]
    (ConnString nvarchar(500),
    Server NVARCHAR(125))

    INSERT INTO ##ConnString

    SELECT
    CASE --Create Connection String for connection string variable
    WHEN s.Server <> i.InstanceName
    THEN CONCAT('Data Source='+s.Server, + '\' + i.InstanceName +';Initial
    Catalog=Database;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;')
    ELSE CONCAT('Data Source=',+ s.Server +';Initial Catalog=Database;Provider=SQLNCLI11.1;Integrated
    Security=SSPI;Auto Translate=False;')
    END AS ConnString,

    CASE --Get server name for server variable
    WHEN s.Server <> i.InstanceName
    THEN CONCAT(S.Server,+'\'+ i.InstanceName)
    ELSE s.Server END AS Server
    FROM dba.Servers AS s --Lists all servers
    INNER JOIN dba.Instances AS i ON i.DBSID = s.DBSID -- joining instances and servers


    SELECT * FROM ##ConnString

    I run execute sql task with this and set the result set to my user::ConnectionStringTable (object variable)

     

    The rest is covered by screenshots below..

     

     

    Okay.. Never Mind. The issue was something different, apparently SSIS is just a liar and wasnt showing me the connection string was updating while it actually was and a different error entirely was preventing it from working.  Thanks 🙂

     

    • This reply was modified 4 weeks ago by  oogibah. Reason: derp
    Attachments:
    You must be logged in to view attached files.
  • anthony.green

    SSC Guru

    Points: 112352

    IIRC you can only store one item in the object variable, so it has to be the connection string or the server.

     

    I tried with server and catalog and it wouldn't work, (happy to stand corrected), so you would only want to pull out the fully built connection string from the table and use that for looping

  • oogibah

    Right there with Babe

    Points: 792

    I was able to store multiple values from my sql server table in the object variable, I'm currently storing: Connection String, Server Name, Server ID and Instance ID.

    In reality the only thing I was doing wrong was making the assumption that during debug mode I would be able to see the connection string update.

  • anthony.green

    SSC Guru

    Points: 112352

    Well I will go back and re-look at my code see if I can perfect it in anyway to do ADO and OLE connections strings, always nice to have multiple streams.

     

    As for the debug, yeah that's something I hit a snag with, adding a watcher to the variable change event let me see it in the watcher window and I could see the variable changing

  • oogibah

    Right there with Babe

    Points: 792

    Yeah, I just finished it, added checks for the conncetion strings to make sure it can connect and if it cant to log what it cant connect to. I did run into another snag where it said it was truncating an id and I was literally like THE ID IS 10 HOW ARE YOU TRUNCATING 10 WTF, turns out when it made it a string it set the length to 1... took me longer than I would like to admit to find that.  Had to go into the dataflow task and actually modify the output columns.

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

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