SSIS 2008 set variable or connection string using dtexec

  • I have an SSIS package and I would like to set the flat file source connection string on the fly. However, I have found that 2008 version does not allow setting parameters that have been set at design time. I have tried setting the connection string directly and also through an expression using a variable. The problem is that to create a variable, I need to create it in the visual studio editor. Then it has a default variable of "" or null. Therefore setting it through dtexec will fail. Removing the variable and assigning it a value in configuration file throws an error b/c the object cannot be found. I have read the various articles about how 2008 is different in this regard but I have yet to find a clear and complete example of setting a variable (or connection string) using dtexec for 2008. In other words, how can we set a variable that does not exist? Or, how can we create a non-design-time variable?

  • I'm a little confused by your problem..

    When you create the variable set it to a VALID value then setup whatever needs to use it. You cannot modify a package much at run-time, you can adjust many parameters but getting the syntax right is usually the challenge.

    Simple point you can't ADD a variable or any other component at run-time, you can only tweak existing objects .

    CEWII

  • Elliot --- thanks for your reply. The problem is that once the variable is added, it cannot be changed at run time b/c the new behavior is such that the value is changed and then changed right back to the original design-time value. So I create a variable and do not set any value. At run time, I use SET to set the value using dtexec. This changes the value, which is changed back to "" and then the package is executed. The syntax was picky (case sensitive) but basically I can just copy the path from the XML configuration setup wizard.

  • devin.sills (2/14/2012)


    Elliot --- thanks for your reply. The problem is that once the variable is added, it cannot be changed at run time b/c the new behavior is such that the value is changed and then changed right back to the original design-time value. So I create a variable and do not set any value. At run time, I use SET to set the value using dtexec. This changes the value, which is changed back to "" and then the package is executed. The syntax was picky (case sensitive) but basically I can just copy the path from the XML configuration setup wizard.

    The variable holds whatever you have set it to for the duration of the run.

    The design-time default is unchanged.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • OK so here is what I am doing:

    create variable named test --- no value assigned, string datatype

    right click flatfile connection, properties, expression, add expression as ConnectionString to the test variable.

    So the ConnectionString should equal to variable value when the package is executed.

    Run the following to run the package:

    declare @ret_code int;

    exec @ret_code = xp_cmdshell 'dtexec /file "test.dtsx" /decrypt xxxxxxxxxxxxx /set \Package.Variables[User::test].Properties[Value];test.txt';

    select @ret_code;

    Description: Cannot open the datafile "".

    The error I am expecting is:

    Description: Cannot open the datafile "test.txt".

    Or if I supply a correct path, then the package should execute w/o errors.

    \Package.Variables[User::test].Properties[Value], \Package.Variables[User::test].Value, \Package.Variables[test].Value, and \Package.Variables[test].Properties[Value] were all attempted but did not set the variable at run time.

  • devin.sills (2/14/2012)


    OK so here is what I am doing:

    create variable named test --- no value assigned, string datatype

    right click flatfile connection, properties, expression, add expression as ConnectionString to the test variable.

    So the ConnectionString should equal to variable value when the package is executed.

    Run the following to run the package:

    declare @ret_code int;

    exec @ret_code = xp_cmdshell 'dtexec /file "test.dtsx" /decrypt xxxxxxxxxxxxx /set \Package.Variables[User::test].Properties[Value];test.txt';

    select @ret_code;

    Description: Cannot open the datafile "".

    The error I am expecting is:

    Description: Cannot open the datafile "test.txt".

    Or if I supply a correct path, then the package should execute w/o errors.

    \Package.Variables[User::test].Properties[Value], \Package.Variables[User::test].Value, \Package.Variables[test].Value, and \Package.Variables[test].Properties[Value] were all attempted but did not set the variable at run time.

    If you're setting the filepath dynamically in this case, I would suggest that you set the "DelayValidation" property to false for both the flat file connection manager and the flat file source in your data flow.

    What is probably happening here is that SSIS is trying to validate the connection before overwriting the variable's value, and hence the error message.

  • Martin Schoombee (2/14/2012)


    If you're setting the filepath dynamically in this case, I would suggest that you set the "DelayValidation" property to false for both the flat file connection manager and the flat file source in your data flow.

    What is probably happening here is that SSIS is trying to validate the connection before overwriting the variable's value, and hence the error message.

    I think you might be onto something.

    CEWII

  • Got it working --- the configuration file used to find the right path to a variable or property should NOT be created as this overwrites any ad hoc values provided at run time. So, after I removed the configuration from the package, it worked:

    declare @ret_code int;

    exec @ret_code = xp_cmdshell 'dtexec /file "test.dtsx" /decrypt xxxxxxxxxxxxx /set \Package.Variables[test].Properties[Value];test_flatfile.txt';

    select @ret_code;

    For others who may face the same issue:

    I created a variable, used expressions to set the connectionstring to the variable. Then I used the SSIS Package Configurations to go through the steps to create a configuration that would set the value of the variable. In the last step, it shows the path to the variable (\Package.Variables[test].Properties[Value]). I did not create the configuration file. Then the above code worked.

    My fallback method was going to use a standard filename in the package and change the ad hoc filename on the fly through xp_cmdshell dos command to the standard filename, run the package, then change it back. Seems more simple than messing with dtexec but at least now I know it is possible to set the connectionstring/variable at run time.

    Thanks everyone for your help!

  • That makes sense devin.. I believe the order of precedence is:

    1. design-time values

    2. run-time startup values

    3. config values (files, SQL, environment, etc.)

    4. execution values

    CEWII

  • Hello, I'm trying to do something exactly like this but I am new to all of this (created my first SSIS package yesterday!) and am having trouble following along with this as far as how to set up the connection string in the Flat File Connection Manager to refer to the variable. Any help that you can give me on this would be much appreciated! Thanks.

  • I have definitely been there! SSIS can have a steep learning curve in some places.

    Anyway, in your package, click the SSIS menu and click Variables. Add a string variable called "db" or something like that.

    Then right click your connection manager, and click Properties. Click Expressions. To the far right you'll see a button with "...", click this.

    Now you should see a window pop up with a grid. This grid has 2 columns, Property and Expression. In the Property column, choose ConnectionString from the drop down. Click the "..." button for the Expression column to set the Expression. Another window will pop up. To the left is a list of variables and parameters. The to right is a list of functions and operators. Under these is the expression box. Here's where you can set the value of your connection string. Mine looks like this:

    "Data Source=" + @[User::server] + ";Initial Catalog=" + @[User::database] + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

    So I have 2 variables: server and database. You can see (and drag and drop) your variables by expanding the variables list to the left. You can also click Evaluate Expression to see what your expression would currently evaluate to if you set your variables to anything.

    Now, setting these via dtexec is done like this:

    dtexec.exe /file "DISK:\PATH\PACKAGE.dtsx" /decrypt package_password /set \Package.Variables[database].Properties[Value];test /set \Package.Variables[server].Properties[Value];mssql\mssql

    In this example, the package is at "DISK:\PATH\PACKAGE.dtsx" with a password of "package_password". The connection manager will try to connect to the "test" database on the "mssql\mssql" server.

  • I have definitely been there! SSIS can have a steep learning curve in some places.

    Anyway, in your package, click the SSIS menu and click Variables. Add a string variable called "db" or something like that.

    Then right click your connection manager, and click Properties. Click Expressions. To the far right you'll see a button with "...", click this.

    Now you should see a window pop up with a grid. This grid has 2 columns, Property and Expression. In the Property column, choose ConnectionString from the drop down. Click the "..." button for the Expression column to set the Expression. Another window will pop up. To the left is a list of variables and parameters. The to right is a list of functions and operators. Under these is the expression box. Here's where you can set the value of your connection string. Mine looks like this:

    "Data Source=" + @[User::server] + ";Initial Catalog=" + @[User::database] + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"

    So I have 2 variables: server and database. You can see (and drag and drop) your variables by expanding the variables list to the left. You can also click Evaluate Expression to see what your expression would currently evaluate to if you set your variables to anything.

    Now, setting these via dtexec is done like this:

    dtexec.exe /file "DISK:\PATH\PACKAGE.dtsx" /decrypt package_password /set \Package.Variables[database].Properties[Value];test /set \Package.Variables[server].Properties[Value];mssql\mssql

    In this example, the package is at "DISK:\PATH\PACKAGE.dtsx" with a password of "package_password". The connection manager will try to connect to the "test" database on the "mssql\mssql" server.

Viewing 12 posts - 1 through 11 (of 11 total)

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