none
SSIS file variable question

    Question

  • I have a dynamic source file as Order20110309A.txt which changes every day and it's in the format of OrderYYYYMMDDA.txt
    I think I should create a variable for the file and then create an expression, but not sure how I should write the expression in SSIS
    any ideas? Thanks.
    sqldev
    Wednesday, March 09, 2011 5:38 PM

Answers

  • You can probably get away with implementing a ForEach Loop to process it.

    The file name will be picked up by a variable in the loop, the expression will be needed.

    I will refer you to an article showing this in details:

    http://www.rafael-salas.com/2009/05/ssis-foreach-loop-looping-through.html


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, March 09, 2011 5:59 PM
    Moderator

All replies

  • You can probably get away with implementing a ForEach Loop to process it.

    The file name will be picked up by a variable in the loop, the expression will be needed.

    I will refer you to an article showing this in details:

    http://www.rafael-salas.com/2009/05/ssis-foreach-loop-looping-through.html


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, March 09, 2011 5:59 PM
    Moderator
  • I would think something like this (rearrange for your specific format):

    "Order" + (DT_STR, 2) DATEPART( "MM" , GETDATE() ) + (DT_STR, 2) DATEPART("DD", GETDATE()) +  (DT_STR, 2) DATEPART( "YYYY" , GETDATE() ) + "A.txt"

    However, this give me an error when evaluating:

    Attempt to parse the expression ""Order" + (DT_STR, 2) DATEPART( "MM" , GETDATE() ) + (DT_STR, 2) DATEPART("DD", GETDATE()) +  (DT_STR, 2) DATEPART( "YY" , GETDATE() ) + "A.txt"  " failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

     (Microsoft.DataTransformationServices.Controls)

    Pretty vague message - SSIS could use some work in this area!  Building expressions in whatever 'language' this is, is quite painful.

    Check this out:  http://pragmaticworks.com/cheatsheet/

    Wednesday, March 09, 2011 6:00 PM
  • Here we go - working now.

    "Order" + (DT_WSTR, 2)DATEPART( "mm" , GETDATE() ) + (DT_WSTR, 2) DATEPART("dd", GETDATE()) +  (DT_WSTR, 4) DATEPART( "yyyy" , GETDATE() ) + "A.txt" 

     

    Wednesday, March 09, 2011 6:07 PM
  • Go for the foreach loop. Here is an other example:
    http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Wednesday, March 09, 2011 6:13 PM
    Moderator
  • Have a look at my post to set connection string dynamically.

    To loop files in a folder.


    My Blog    |    Ask Me
    Wednesday, March 09, 2011 6:30 PM