Post reply

Parameterize connection manager

  • October 2, 2018 at 5:22 pm

    #364300

    I have central server and multiple other server ( where I want to run the job) to extract some data after executing couple of Stored procedure . 

    I want to know is there any way to Parameterize connection for data source at run time for the job which pull the ssis package from the central server .

  • November 5, 2018 at 7:58 am

    #2012398

    RatanDeep Saha - Tuesday, October 2, 2018 5:22 PM

    I have central server and multiple other server ( where I want to run the job) to extract some data after executing couple of Stored procedure . 

    I want to know is there any way to Parameterize connection for data source at run time for the job which pull the ssis package from the central server .

    You could set up multiple SQL Agent Jobs each with a different connection.

    Or you could store the connections in a local SQL table and use a ForEach loop in SSIS to change the connection parameters on each loop.

  • November 5, 2018 at 10:52 am

    #2012429

    Yes, I do something exactly like that against my own CMS servers.

    I create a SQL connection manager, and in expressions, I point the servername property to a user variable.

    then a for each server in my query from CMS assigns that variable, so each iteration of the loop ends up pointing to the master db on each server, and i can query whatever stats or info i care to grab.
    i'll use three part names for grabbing things like msdb backup history, but often i'm executing something in master in my case, which is the one db i know always exists.


    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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