May 5, 2011 at 4:01 am
I am loading a number of .txt files into a table through SSIS using Looping (load all the files in a folder). Is there a way to identify already loaded files (such as getting the filenames and loading it into a table and setting a flag)? Because the files may arrive at inconsistent intervals so we have to load newly arrived file without loading old files.
May 5, 2011 at 5:52 am
Sure, easy enough.
As you're looping through the files, make the first step in the container an execute SQL task that passes the filename to a stored procedure and outputs a exists/doesn't exist variable of some kind, then instead of using a success constraint to go to the data flow task, make it based on an expression, using the output variable from the Execute SQL Task.
Then after the file is succesfully loaded, go to another Execute SQL Task to insert the filename into your tracking table.
FYI, using an ADO.Net connection for the execute SQL Task rather than OLEDB makes dealing with input/output variables a little nicer.
May 5, 2011 at 7:08 am
Thanks howard.
The downside is that I have very limited/nil front end tech knowledge. So to make my request clear: A folder has number of txt files. Package runs and loads all the files which was present at that time. But if the pkg runs again, then it should not load the already loaded file. How to tell the pkg that some files should not be loaded when I am using For each/for loop container? or Should i not use for each/for loop container itself? Any other way to achieve this welcome.
As said, i have limited knowledge of vb,c# etc.
May 5, 2011 at 7:32 am
Doesn't require any VB/C# knowledge at all, just a small amount of T-SQL to maintain a table of file names. a For Each Loop Container is as good a way to do it as any assuming you have some understanding/willingness to learn SSIS control flows.
The following resources should help:
How to execute a stored procedure and get an output parameter:
http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters
Information about using precedence constraints (look at the Defining Workflow by Expressions section specifically).
May 5, 2011 at 1:13 pm
A few options:
1. At the beginning of the processing of a file, enter it into a database table. At the end of processing, set a flag, as you suggested, so that future loads would not try to load this file. Of course, this would require a little more processing than just reading the files from a folder. You would have to employ some logic.
2. At the end of processing the file, move it to a subfolder.
As they say, "there are many ways to skin a cat". These are just two of several options, I am sure.
Hope that this helps.
Thanks...Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply