SSIS – concatenate variables with SQL query

In previous post, SSIS – dynamic file name I was generating dynamic file name.
Later in my project I had combine it with dynamic file path, stored in variable.
For this scenario I will reuse previous project. All I have to do is add existing variable which stores file location to Parameter Mapping.

ssis01

Does not matter how many parameters you have, just remember to number them correctly (0, 1, 2…) because we are going to use parameter mapping (?
) in our SQL query

All we have to do is modify our query like below. Notice how @Path gets value. ? represents FileLocation variable.

DECLARE @Path Varchar (256)
SET @Path = ?

SELECT @Path + 'SomeFile_' +
CAST(DATEPART(yyyy,GetDate()) AS NVARCHAR(4)) +
CAST(RIGHT('0' + DATEPART(mm,GetDate()) ,2) AS NVARCHAR(2)) +
CAST(RIGHT('0' + DATEPART(dd,GetDate()) ,2) AS NVARCHAR(2)) +
'.xls' as SQLFileName

--or

@[User::FileDestination] + @[User::FileNamePrefix] 
+ (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) + ".csv"

Leave a Reply

Your email address will not be published. Required fields are marked *