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.

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.

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.