1

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Madhu
  • 45
  • 6
  • Cast both as DT_STR first? Then use an expression to combine the variable and the file name for the connectionstring property. It's hard to give a detailed answer as you haven't provided specifics. – Jacob H Feb 26 '20 at 19:20

1 Answers1

2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    https://www.red-gate.com/simple-talk/sql/ssis/implementing-foreach-looping-logic-in-ssis/ – Madhu Feb 27 '20 at 15:12