Variable Source Connection in SSIS (2008 R2)

• dshuler99

  Grasshopper

  Points: 11

  More actions

  February 21, 2012 at 12:37 pm

  #149080

  Hi All,

  This is my first post here at SQLServerCentral. I am trying to automate the backup of several files daily. After completing several tutorials, I have run into a "real-world" problem that I the tutorials failed to address-- variable source connections. In other words, my source files have a different name each day. All of the examples I see in my books or online have a static source file name.

  Is is possible to connect to a folder and pull new files each day? For example, the files for 4 consecutive days would be found in a folder as below:

  ABC20120218.html

  ABC20120219.html

  ABC20120220.html

  ABC20120221.html

  All of these files have the same prefix with the only change in file name being the date.

  I cannot figure out how to configure my SSIS package to account for a changing filename. Can someone help me set this up properly? I am running SQL Server 2008 R2 BIDS.

  Thanks in advance!

• Martin Schoombee

  SSCoach

  Points: 19128

  More actions

  February 21, 2012 at 12:45 pm

  #1449922

  dshuler99 (2/21/2012)

  ---

  Hi All,

  This is my first post here at SQLServerCentral. I am trying to automate the backup of several files daily. After completing several tutorials, I have run into a "real-world" problem that I the tutorials failed to address-- variable source connections. In other words, my source files have a different name each day. All of the examples I see in my books or online have a static source file name.

  Is is possible to connect to a folder and pull new files each day? For example, the files for 4 consecutive days would be found in a folder as below:

  ABC20120218.html

  ABC20120219.html

  ABC20120220.html

  ABC20120221.html

  All of these files have the same prefix with the only change in file name being the date.

  I cannot figure out how to configure my SSIS package to account for a changing filename. Can someone help me set this up properly? I am running SQL Server 2008 R2 BIDS.

  Thanks in advance!

  If you know what the filename will be before execution, you could first set a variable's value, and then set the connection manager's properties with an expression. Be sure to also set the "DelayValidation" in that case as the file (value in the variable) may not always exist when the package tries to validate.

  Your other option could be to use the "ForEach" component to loop through all the files in a folder, and deal with them appropriately.

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  February 22, 2012 at 12:27 am

  #1450086

  Martin Schoombee (2/21/2012)

  ---

  Your other option could be to use the "ForEach" component to loop through all the files in a folder, and deal with them appropriately.

  The For Each Loop is the way to go.

  You can use a wildcard in the configuration of the For Eeh, for example ABC*.html.

  That way it will pick up all your different files.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP