I have a Project parameter called Databases which is a Project parameter in SSIS of data type String. The variable in itself contains a hard coded XML document, i.e.:
<dbs>
<data>
<db>database1</db>
<store>store1</store>
</data>
<data>
<db>database2</db>
<store>store2</store>
</data>
</dbs>
and I use it in a foreach loop in SSIS where I iterate these databases and corresponding stores.
My foreach loop is configured as:
DocumentSourceType: Variable
DocumentSource: $Project::Databases
EnumerationType: ElementCollection
OuterXPathStringSourceType: DirectInput
OuterXPathString: /dbs/*
InnerElementType: NodeText
InnerXPathStringSourceType: DirectInput
InnerXPathString: *
and I then use Variable Mappings to have index 0 correspond to the variable User::DatabaseName and index 1 to correspond to User::StoreKey.
This works when I execute the package from SSIS. The SQL statement variable I use with these values gives the correct output etc.
The problem is when I try to execute this using a SQL Agent Job I get the error:
Failed to lock variable "$Project::Databases" for read access with error 0xC0010001 "The variable cannot be found". This occurs when an attempt is made to retrieve a variable from the Variables Collection on a container during execution of the package, and the variable is not there. The Variable name may have changed or the variable is not being created
but the variable should be created - it is merely a hard coded variable after all.
When I have googled this issue possible solutions has been that:
The variable is not of type string (which it is in my case)
or
The user from which the SQL Agent Job is executed from does not have the correct permissions (in my case it is ran by a sysadmin on the database for which I read the data to and msdb and SSISDB).
SSIS will only use Project parameters when the package is deployed using the Project Deployment Model. If you're using the Package Deployment Model, you should change your package to use a configuration instead of Project parameters.