Tag Archives: SSIS

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"

SSIS – dynamic file name (date as file name)

We will be using Execute SQL Task for that, quick and easy.
First we need a variable, say FileName with type String

ssis01

Properties of SQL Task:

ssis02

  • Connection: create SQL Connection to any database, does not matter which one
  • SQLSourceType: Direct Input – we will create simple inline query
  • SQLStatement: click on 3 dots (…) and paste query below:
    SELECT '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
    

    File will be called SomeFile_date.xls and saved with SQLFileName alias which will be accessing in next step

  • ResultSet: Single Row
  • Switch to Parameter Mapping of SQL Task.

    We will be mapping to previously created FileName variable
    Type VARCHAR, Parameter 0

    ssis03

    Now we need to set our FileName variable value

    • Switch to Result Set
    • click Add
    • type SQLFileName (our previously created alias in SQL Statement)
    • select User::FileName viariable

    ssis04

    To test it, create Script Task

    And connect with our SQL Task:

    ssis05

    Map ReadOnlyVariable property with our FileName variable and click Edit Script

    ssis06

    After code editor shows up, find line saying

    // TODO: Add your code here

    and paste line below

    MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());

    ssis07

    Now you can simply close script editor.
    Task execution will pop up window with file name

    ssis08