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

Leave a Reply

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