• 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.