SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 2008 set variable or connection string using dtexec


SSIS 2008 set variable or connection string using dtexec

Author
Message
blueazul
blueazul
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 319
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?
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24170 Visits: 5314
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
blueazul
blueazul
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 319
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51945 Visits: 21170
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
blueazul
blueazul
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 319
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.
Martin Schoombee
Martin Schoombee
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4245 Visits: 4546
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.



Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24170 Visits: 5314
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
blueazul
blueazul
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 319
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!
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24170 Visits: 5314
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
dthayer
dthayer
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 2
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search