Posted Tue, 24 Oct 2023 02:04:13 GMT by Michel Lalancette
I'm trying to create a data flow component with an OleDbSource using a variable as a query source.
I know for a fact that the variables end up being created because if I use direct input instead as a test, they show up in the variables panel.

I'm getting the following error:

Error    20   
Could not resolve reference to '@[User::EMPLOYMENT_SelectStatement]' in property 'Variable'. '' is invalid. Provide valid scoped name. Property Variable.

<Variables>
    <#
    // ##### For each table to replicate, create a variable that contains the select statement#####
    foreach(DataRow tableReplicate in tableReplicateList.Rows)
    {
        string tableSourceSystemName = Util.ConvertFromDBVal<string>(tableReplicate[0]);
        string tablePackageGroupName = Util.ConvertFromDBVal<string>(tableReplicate[12]);
        if (!tableSourceSystemName.Equals(sourceSystemName) || !tablePackageGroupName.Equals(packageGroupName))
        {
            continue;
        }  
        #>
        <Variable Name="<#=Util.ConvertFromDBVal<string>(tableReplicate[5])#>_SelectStatement" DataType="String" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">
            <#=Util.ConvertFromDBVal<string>(tableReplicate[10])#>
        </Variable>
        <Variable Name="<#=Util.ConvertFromDBVal<string>(tableReplicate[5])#>_LastLoadTS" DataType="String" IncludeInDebugDump="Exclude">
            1999-01-01 12:00:00
        </Variable>
    <#
    }
    #>
</Variables>


<OleDbSource Name="Select <#=Util.GetDisplaySourceTableName(tableReplicate)#>" ConnectionName="SQL Kronos">
    <VariableInput VariableName="@[User::<#=Util.ConvertFromDBVal<string>(tableReplicate[5])#>_SelectStatement]"/>
</OleDbSource>
Posted Wed, 29 May 2024 09:37:33 GMT by Roy Miles
Did you check if the variable EMPLOYMENT_SelectStatement exists in your SSIS package? Sometimes variables might not get created properly or might be deleted accidentally.

More information via: snake game@gmail.com

You must be signed in to post in this forum.