August 5, 2010 at 9:48 am
I am trying to set up a flat file connection the connect to the appropriate flat file in a directory with the use of a wild card.
There will only be one file to load in the folder given its type.
Example:
C:\folder\check_20100808_filetype.dat
The variable that is not constant is the date, and it may not be the current date so i cant use a regex to populate the date in there.
The expression should look like
C:\folder\check_*_filetype.dat
Where it grabs that file from the folder and uses it for the connection.
Again there will only be one file in the folder at a time with that exact file type to find..
I can't do a for loop on the folder to find all the files and populate the name dynamically, isn't there a way to just set the connection string in the expressions to something like that above where it actually grabs the file and uses it?
August 5, 2010 at 9:55 am
You cannot use a wildcard in your connection properties.
Use a FOREACH loop and put your wildcard file-spec in there. Map the file path returned from the foreach loop to a string variable and use that variable in an expression to set your file connection.
The FOREACH loop will then execute only as many times as the file-spec is satisfied within the folder you specify (ie, once).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2010 at 10:11 am
So, I assume a for-each loop will be needed for each filetype...
I have done it this way before but just becomes a PAIN when you have 15 for loops you have to set up.
August 5, 2010 at 10:44 am
You did not mention this in your original post.
You need a different connection for every combination of flat file 'type' you have (by type, I am referring to the number of fields, field data types, order of fields.
SSIS does not handle a dynamic connection to multiple flat files with different meta data (if you know what I mean).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2010 at 11:15 am
geilecj (8/5/2010)
So, I assume a for-each loop will be needed for each filetype...I have done it this way before but just becomes a PAIN when you have 15 for loops you have to set up.
Other work around to use dos command something like EXEC xp_cmdshell 'Dir C:\folder\*.dat' and store that in a table. You can filter the output to different variables based on the format you like and point to the flat file connection directly to that variable. Complexity wise painful than the foreach logic and also need to make some admin related changes.
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply