January 17, 2014 at 11:35 am
I'm just trying to tackle some stuff with SSIS, and it is not proving to be easy. I guess I need a new way of thinking.
Here is what I need to do:
1) on a periodic basis, check a particular subdirectory, and look for files which match "CCO_Forecast_20*.xls" where that "20*" wildcard would be a date, such as "20140117". The wildcard part will change every day. I know I can use an external task scheduler to trigger the dtsx package to run.
2) if I find matching files, import them sequentially into a holding table in SQL Server, and perform further processing before the next file is imported. I know I can use a ForEach File Loop as a container for this.
3) include the file name, and portions of the file name, as derived columns in the imported table. after that I assume I could run T-Sql tasks, but SSIS is not really matching what I think it ought to do, coming from a procedural programming background. I'm having problems getting to the imported file name variable. My code worked at one point, then quit.
4) Move the Excel file to an archive folder.
The immediate question I have is, how do I set up a datasource without an exact file name? Or how to I change the name programmatically, for each cycle?
Any other details on the other steps would be helpful, as I don't know what I'm going to run into yet.
January 18, 2014 at 5:33 am
Have a look here for an example of importing multiple files with the same format.
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
January 18, 2014 at 11:58 am
Excellent. Thank you. I found something similar, but they said just to put the file name in the connection string, which, does not work.
I was also running into problems with concatenation on an expression I was trying to do, so, this will likely prove most helpful. Thank you.
January 21, 2014 at 2:57 pm
IGNORE THIS FOR NOW. It's a scope issue. Forum won't let me delete the message.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply