How to store 'fully qualified' and 'name only' file name in SSIS variable

sql-server variables ssis etl foreach-loop-container
chris1982 picture chris1982 · Jan 30, 2015 · Viewed 10k times · Source

I have an SSIS package that has a Foreach Loop container loading all .txt files in a static folder. I'm passing the fully qualified file name as a variable which I'm using in the connection string.

I now need to pass just the filename to a variable to use to execute a stored procedure, trouble is if I change the Foreach Loop collection to just retrieve the filename it prevents the rest from working.

Is there a way to pass both the fully qualified and name only file name to a variable in ssis?

Answer

billinkc picture billinkc · Jan 30, 2015

Nope, you get to pick one: either fully qualified or file name only. Personally, if everything except the stored procedure expects fully qualified name, I'd create a second variable FileNameOnly and either populate it with an expression like

RIGHT(@[User::CurrentFileName], FINDSTRING(REVERSE(@[User::CurrentFileName]), "\\", 1 )-1)

(this assumes you've stored the value in a variable called CurrentFileName)

Or you can use a script Task to assign the value to FileNameOnly and leverage the .NET System.IO.Path.GetFileName method.

Related questions

SQL variable to hold list of integers

I'm trying to debug someone else's SQL reports and have placed the underlying reports query into a query windows of SQL 2012. One of the parameters the report asks for is a list of integers. This is achieved on the report …

Read More
How to return the output of stored procedure into a variable in sql server

I want to execute a stored procedure in SQL Server and assign the output to a variable (it returns a single value) ?

Read More
SQL NVARCHAR and VARCHAR Limits

All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for NVARCHAR(…

Read More