CODEWITHSUNDEEP
sqlsql-servercsvssis
Looking_for_answers
Looking_for_answers

Reputation: 343

How do I Load the file name into a new column through SSIS, using variables?

I have a set of files that are dynamically changing, I am using the foreach loop container to update them.

I am using two variables (1) for file path and (2) for source folder.

I need to insert the file names into a separate column.

File names:

XYZ0000_2016_04_Application_Report.csv
ABC0000.10_2016_04_Application_Report.csv

I need to insert only XYZ0000 into a new column.

How do I do it with the help of variables?

Upvotes: 0

Views: 1646

Answers (2)

iamdave
iamdave

Reputation: 12243

Add a Derived Column transformation to your data flow and use the following expression:

LEFT(@[User::FileName],FINDSTRING("_", @[User::FileName], 1))

This assumes you are recording your file name from the Loop container

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

So, try this:

SELECT SUBSTRING(yourfield, 0, CHARINDEX('_', yourfield))
FROM yourtable

Upvotes: 1

Related Questions