Integration Services. Importing xls with wildcard in name. Adding file name to the data source.

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  March 2, 2011 at 10:17 am

  #247550

  Hi,

  Right

  if you are importing an xls file using Integration services 2005 you use the Excel connection However you don’t seem to be able to set up a wildcard for the file name

  for example every month there is a new Contacts file but the files have a different data extension. I want to be able to just grab the file if its like Contacts*.xls

  For some reason I cant seem to do this.

  Also after this I want to grab the filename and add it as a column to the data. Ive been looking at lots of advice about it but I cant quite seem to get what I need.

  Any help would be appreciated

  Debbie

• Koen Verbeeck

  SSC Guru

  Points: 259115

  More actions

  March 3, 2011 at 5:12 am

  #1293931

  The wildcard functionality is available in the For Each Loop, so you can look into that.

  The For Each loop also allows you to store the filename of the file it is currently processing into a variable.

  Use a derived column in the dataflow to add this filename as a new column.

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