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


Properties of SQL Task:


  • 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


    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


    To test it, create Script Task

    And connect with our SQL Task:


    Map ReadOnlyVariable property with our FileName variable and click Edit Script


    After code editor shows up, find line saying

    // TODO: Add your code here

    and paste line below



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


Leave a Reply

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