Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS 2008 set variable or connection string using dtexec Expand / Collapse
Author
Message
Posted Tuesday, February 14, 2012 12:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 9:07 AM
Points: 47, Visits: 299
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?

Post #1252103
Posted Tuesday, February 14, 2012 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1252132
Posted Tuesday, February 14, 2012 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 9:07 AM
Points: 47, Visits: 299
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.

Post #1252136
Posted Tuesday, February 14, 2012 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 5,189, Visits: 12,052
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1252138
Posted Tuesday, February 14, 2012 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 9:07 AM
Points: 47, Visits: 299
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.

Post #1252152
Posted Tuesday, February 14, 2012 2:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:57 PM
Points: 317, Visits: 3,615
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.



Post #1252157
Posted Tuesday, February 14, 2012 2:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1252169
Posted Tuesday, February 14, 2012 2:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 9:07 AM
Points: 47, Visits: 299
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!
Post #1252170
Posted Tuesday, February 14, 2012 3:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #1252176
Posted Wednesday, August 8, 2012 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 9, 2012 9:53 AM
Points: 1, 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.
Post #1342189
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse