0

I have an SSIS package that picks up a file from a directory and imports it into a database, pretty straightforward stuff. The only issue is the input file is named based on the current date, e.g \path\to\file\filename_010115.txt, where 010115 is for January 1, 2015.

I was calling this package with a custom bat file that set the connection manager for a Flat File Source to the current date formatted filename and passed it into dtexec.exe directly; however, our environment demands that we use XML configuration files and not bat files. Is there a simple way to set the file source "prefix" in the xml and append the date-formatted filename before attempting to pick up the file?

Robin
  • 1,074
  • 8
  • 29

1 Answers1

0

Have you considered rearchitecting your approach? You're not going to be able to gracefully do this with a Configuration - be it XML, table, environment, or registry key. In the simplest case, a table, before you could even start your SSIS package, a process would need to update that table to use the current date. A scheduling tool like SQL Agent can run SQL Commands. If you're going the XML route though, you're looking at a tiny little app or PowerShell command to modify your configuration file. Again, that's going to have to modify your file every day and before the SSIS package begins as it sets values once in the beginning and never consults the configuration source again.

You could use an Expression in SSIS to use the current date as part of your flat file source's connection string but in the event you need to process more than one day's file or reprocess yesterday's file, you're humped and either have to manually rename source files or change the system clock and nobody's going to do that.

A more canonical approach would be to use a Foreach (file) Loop Container. Point it at the source folder and find all the file(s) that match your pattern. I'm assuming here you move processed files out of the same folder. The Foreach Container finds all matching files and enumerates through the list popping the current one into whatever variable you've chosen.

See a working example on

Community
  • 1
  • 1
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I do not have any of the use cases you suggested, but do have the restrictions I posted... I did solve the problem using a custom variable and an expression in SSIS shortly after I asked the question. I'm not a SQL Developer and was not too familiar with SSIS package development (I came here looking for some direction to learn a new tool), but I managed to find the expressions feature and got it to work. – Robin Jan 11 '15 at 00:45