CODEWITHSUNDEEP
c#sqlsql-servervisual-studio-2012ssis
Aaron Lelevier
Aaron Lelevier

Reputation: 20780

SSIS use part of a filename as a variable within a Foreach Loop Container

I'd like to SSIS use part of a filename as a variable within a foreach loop.

Within a Foreach Loop Container I’d like to take a filename, i.e. 7788-Trailer Park Boys, and to split at the “-“ mark, and set the 7788 as a variable.

Then I’d like to use that variable to update a table referenced within the Foreach Loop Container. So, change all columns in one table to 7788. Can this be easily done in SSIS without custom scripting?

If it can, what SSIS components would I need to use?

Thank you in advance.

Edit

I got it working using this link:

This link helped me get it working: http://sqlage.blogspot.com/2013/11/ssis-use-variable-in-execute-sql-task.html.

Here is the code that I used in the Execute SQL Task:

declare @promoid varchar(255);

set @promoid = ?

update hr_sandbox.dbo.tbu_tracking_block
set PromoID = substring(@promoid,charindex('blocks\', @promoid)+7, len(@promoid)-charindex('.xlsx', @promoid))

The only problem with my code is that my variable was the full path name, so I had to slice it to get out the file name. Is there an easier way to extract the file name our of a fully qualified file path?

Upvotes: 0

Views: 980

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Yes you can use the execute SQL Task. You will have to map the query parameter to variable within an update statement.

You can look up how to do it here: https://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

Upvotes: 1

Related Questions