November 25, 2009 at 8:10 am
Hey Guys,
I have a SSIS Package that uses filename as the input parameter and stages the file...SSIS runs fine now what I need to do is Create a package that will load the Old files one by one using the package that I talked about... name of the files have date appended to it and the rest of the string remains the same... Guys any idea what is the best approach to do this? The files are zipped...Thanks for taking time and help.
November 25, 2009 at 8:26 am
You can use a for each loop in SSIS to load all the files in the folder.
Search on BOL for details on Foreach loop.
-Vikas Bindra
November 25, 2009 at 12:45 pm
vikas bindra (11/25/2009)
You can use a for each loop in SSIS to load all the files in the folder.Search on BOL for details on Foreach loop.
[font="Comic Sans MS"]
For detailed example:
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
November 25, 2009 at 12:53 pm
There is one issue though... They are historical files and each days files are in folders named as YYYYMMDD and the issue is somedays the files are Zipped while other days the files are not zipped and are .txt format.... I guess I need to create 2 for Each loop (to transverse through subfolders as well) for .txt and .gz files and execute my Staging SSIS Package using dtsexec.... What do you suggest?
November 25, 2009 at 1:13 pm
Ghanta (11/25/2009)
There is one issue though... They are historical files and each days files are in folders named as YYYYMMDD and the issue is somedays the files are Zipped while other days the files are not zipped and are .txt format.... I guess I need to create 2 for Each loop (to transverse through subfolders as well) for .txt and .gz files and execute my Staging SSIS Package using dtsexec.... What do you suggest?
[font="Comic Sans MS"]
Yes - 2 forEachLoop will definitely do the work.
However one ForEachLoop can also do the job - scan all files with format: filenameformat*.* (txt or gz) - store the filename in a variable say fileJustFound.
Then inside the container:
1) Use a script component to evaluate if the filename extension. Say isText (a boolean variable) is false - i.e. it's a *.gz file
2) Use precedence constraint to evaluate the expression isText.
3) If isText = False -- then process the gz file. Say Step A (uncompress) -> Step B -> Step C ...
4) if isText = True -- then process txt file. You may tweak to point the precedence constraint from script task to say Step B
This way you can have maximum reusability..
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply