Search code examples
sqlsql-serverssisetlforeach-loop-container

The data type of object variable is not supported in an expression in ssis

I am retrieving list of ids using sql task and stored in object variable. Passing each id to foreach loop container I am creating csv files. I want the csv file name to be unique. How can we append the id from variable to csv file name?

Thank you

Solution

  • Passing each id to foreach loop container I am creating CSV files. I want the CSV file name to be unique

    You don't have to alter the object variable that stores the whole list of id, since the foreach loop map the current id value to a variable (Variable Mapping tab within the container editor), you can create another variable that is evaluated as an expression to generate a unique name. As an example, consider that @[User::CurrentID] is the variable that stores the current id within the foreach loop. create another variable of type string and evaluate it using a similar expression:

    "D:\\MyCSV_" + (DT_WSTR,50)@[User::CurrentID] + ".csv"

    Note that the flat file connection manager connection string must be evaluated using an expression to read from this variable.

    If @[User::CurrentID] is of type string, you can add an expression task within the foreach loop container to alter its value using a similar expression:

    @[User::CurrentID] = "D:\\MyCSV_" + @[User::CurrentID] + ".csv"

    Helpful links