Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
One file only, file name has date at the end

Answered One file only, file name has date at the end

  • Friday, April 27, 2012 1:23 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am going to have a file I need to upload into SQL daily.  There will always only be one file in the directory, but the date on the end of the file name will change.  For example: File_04272012.  I wouldn't think I need to do a For Each Loop because there will always only be one file, but how do I set it up with the name being variable without using a For Each Loop?  Can someone point me in the right direction?

    Thanks,

    Krysti

     

All Replies

  • Friday, April 27, 2012 1:26 PM
     
     Answered
    Sign In to Vote
    1
    Still use a for each loop, that allows you to easily use the wildcard.  The fact that the container only executes once is no problem and will give you a way to "catch up
    " if your process ever dies for a couple days and you wind up with multiple days worth of files in the directory.

    Chuck

    • Marked As Answer by Krysti211 Friday, April 27, 2012 7:48 PM
    •  
     
  • Friday, April 27, 2012 3:41 PM
     
     
    Sign In to Vote
    0
    Thanks, Chuck.  The only problem with that is if it breaks down for a couple of days I would only want to load the last file, not all of them.  It is so frustrating that SSIS cannot easily deal with wildcards.
     
  • Friday, April 27, 2012 3:43 PM
     
     
    Sign In to Vote
    0
    Thanks, Chuck.  The only problem with that is if it breaks down for a couple of days I would only want to load the last file, not all of them.  It is so frustrating that SSIS cannot easily deal with wildcards.

    If it did deal with wildcards in the filename - then how would you determine which file to use if there was more than one?   You can do anything you want in a script task, so for your situation you could use a script task to look in the directory, pick the file you want and then assign it to a package variable.  Next step would be the data flow that uses that package variable as the filename for a datasource

    Chuck

     
  • Friday, April 27, 2012 4:08 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    You don't need a script task or a Foreach Loop if you don't want to use one.  (I can understand why you don't want a Foreach Loop - but it would still work, wouldn't it?  I also tend to avoid scripts because they're a little opaque.)

    Instead, use an expression on the "Connection String" property to supply the filename with the date.  I'm assuming the date can be known deterministically by when the package is run - i.e. the date is either "today" or "yesterday".  If so, then the expression on the connection string can be a concatenation of the static part of the file pathname, plus conversions of calls to YEAR(GETDATE()), MONTH(GETDATE()), and DAY(GETDATE()) respectively.

    Todd McDermid's Blog Talk to me now on

    • Marked As Answer by Krysti211 Friday, April 27, 2012 7:48 PM
    •  
     
  • Friday, April 27, 2012 7:45 PM
     
     
    Sign In to Vote
    0

    Thanks for all your guys help.  I ended up doing it a different way.  I have a batch file that is copying the file where I need it and I just added a line to rename it without the date.  I'm in a hurry and needed to get this done and I don't know SSIS all that well.  Todd, that would work also - I might use that later on.  Thanks for the help, both of you!!