Create a variable from SSIS Derived Column

Question

In my SSIS package, I have a Script Component that creates a +1 new customer ID when the package runs.

I want to assign the numerical value of the ID to a variable. The variable will be used in a File System Task to create a directory with the Customer ID as the folder name.

Example - C:\Customer Orders\<CO_ID>

I have created a derived column (sequence) transformation in the data flow named (CO_ID). However, I am stuck on the proper syntax for the variable expression.

Any advice is appreciated.

Answer 1

Without the error message and more details, it is difficult to say what's wrong. But I think you're trying to convert a number to a string and then concatenate it to another string (the path). If that's the case, your derived column expression should be something like:

"c:\Customer Orders\" + (DT_STR, 10,1252) CO_ID

If the backslashes give you a problem (can't remember if they need to be escaped), then try doubling them up:

"c:\\Customer Orders\\" + (DT_STR, 10,1252) CO_ID

Answer 2

Derived column Transformation is used inside a Dataflow task, and Execute System Task is on the Control Flow level, so any variable update is not visible to the Control Flow level until the Dataflow Task execution is done. So it will only create a directory for the last CO_ID.

If you need to create a Directory for each customer, and assuming that you are creating ID inside the script Component. Just create the directory from it using the following code (Vb.Net)

If Not Io.Directory.Exists("c:\Customer Orders\" & COID.ToString()) Then

   IO.Directory.CreateDirectory(c:\Customer Orders\" & COID.ToString())

End If

Else, if you only need to create columns and to loop over them later, i think that you only need to Cast the CO_ID column to DT_WSTR:

"C:\\Customer Orders\\" + (DT_WSTR, 50) [CO_ID]