October 11, 2011 at 9:53 am
hii
am loading flat files into sql DB. Flat files are having date as name like Xxxx_20111011.txt.
i wrote expression for that and evrything is good.what If i miss yesterdays file or day before yesterdays file
I need an expression which pulls the the data from file which is having day before yesterdaysfile name(xxxx_20111009) and if i reexecute the package it has to pull the data from yesterdays file(xxxx_20111010) and again reexecuting it has to took todays date file(xxxx_20111011)..
Thank you
October 11, 2011 at 10:06 am
Why not just use a Foreach loop to process all files in the folder, regardless of name?
Or maybe *.txt - whatever works.
Do the files need to be processed in order?
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
October 11, 2011 at 10:15 am
Ya i can do it .but the pro is what if there are many files on same name Ex : xxxxx_20111011,xxxxx_20111010,xxxxx_20111009
What file foreachloop takes??
And everyday file is having new data..i.e,the data from xxxxx_20111010 is different from xxxxx_20111011
October 11, 2011 at 10:20 am
Files in the same folder cannot have the same name.
Best practice is to move your files to an archive folder after processing them - so the only files in the folder should be 'awaiting processing'.
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
October 11, 2011 at 10:28 am
Now manager told me that i have deploy those packages to server , have to create configurations and schdule them.
For dynamically select todays date file am using expression like this
(DT_WSTR, 4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR, 2)DAY(GETDATE()), 2) + ".txt"
If yesterdays job failed .i wanna load yesterdays file again..i can do that by using configurations.
But how can i change above expression for yesterdays date??
Thank you
October 11, 2011 at 12:11 pm
Your approach is fraught with problems. For example, what if the load fails on a Friday and you're trying to load it on a Monday? Yesterday's filename will give you the file for Sunday, not Friday. If you receive a file on Sunday it will load that one, but still won't load the Friday file. If you don't receive a file on Sundays your package may error out.
Phil already told you the correct approach to use: ForEachFile. ForEachFile was specifically designed to handle your situation. By choosing the wrong approach, you're turning an easy problem into a difficult problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2011 at 12:56 pm
Yeah I'll echo drew and phil on this one.
IMO your best bet is to have your data exist in two tables. One table will be something like FileNames, and will simply store a list of the files you have loaded into the database. The other table will be something like FileContents, and will store the data being loaded into the database.
For your SSIS package, you'll have a ForEach loop container, using the File enumerator, and looping through the file contents of a directory.
Within the container, you'll have a few steps. The first step will be to execute an SQL task and look in your FileNames table. If the file name exists in that table, then you can execute a File System Task, and move that file to an archive folder. If the file name does not exist, then you can execute an execute SQL task to write the name of the file to your FileNames table, and then a data flow task to copy the contents of the file to your FileContents table. The FileContents table would have a FK referencing the FileNames table, so you know which file the contents were derived from. Then you would move the file to the archive folder as in the other case.
All in all, you have:
1. ForEach File in Folder
1. a. Check FileNames for existence of File
1. a. 1. File does not exist -- > Copy file contents into FileContents table, adding FileNameID as an FK
1. b. Move file to archive folder
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply