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.
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"