March 2, 2011 at 3:17 pm
I receive our data extracts in .zip format. They are then un-compressed to a clean \working directory.
The file names correspond with the SQL tables they need to be loaded to; but all txt files are optional.
i.e file01.txt if it exists needs to be loaded to sqltable01,
file02.txt --> sqltable02
...
file60.txt --> sqltable60.
Make sense? I'm trying to brain storm different ideas to get these files loaded to my sql tables when any of them may or may not exist (but assuming at least once exists).
Idea A) Since i know the names of all of the possible files (about 60 max). Create a Boolean variable for each table. Run an SSIS Script task to set the Boolean variables to true or false based on System.IO.File.Exists(filePathAndName). From there use a constraint before each possible load that only loads the file based on the expression: (variablename == true).
Idea B) Check each file individually with its own script task. Using this method would not require as many variables be defined, but otherwise it seems about the same. Upon thinking about it, I'm not sure I could get by with less variables... as I don't think I want to pass the same variable to 60 different script tasks at the same time.
Idea C) Find some way to leverage the For Each File task. I'm trying to think of a way to use this, but nothing is coming to mind that seem to take up a lot of overhead; or be a real PITA to utilize.
Suggestions? Ideas? Comments on any of the above ideas?
Regards,
Bob McC
March 2, 2011 at 3:42 pm
BobMcC (3/2/2011)
I receive our data extracts in .zip format. They are then un-compressed to a clean \working directory.The file names correspond with the SQL tables they need to be loaded to; but all txt files are optional.
i.e file01.txt if it exists needs to be loaded to sqltable01,
file02.txt --> sqltable02
...
file60.txt --> sqltable60.
Make sense? I'm trying to brain storm different ideas to get these files loaded to my sql tables when any of them may or may not exist (but assuming at least once exists).
Idea A) Since i know the names of all of the possible files (about 60 max). Create a Boolean variable for each table. Run an SSIS Script task to set the Boolean variables to true or false based on System.IO.File.Exists(filePathAndName). From there use a constraint before each possible load that only loads the file based on the expression: (variablename == true).
Idea B) Check each file individually with its own script task. Using this method would not require as many variables be defined, but otherwise it seems about the same. Upon thinking about it, I'm not sure I could get by with less variables... as I don't think I want to pass the same variable to 60 different script tasks at the same time.
Idea C) Find some way to leverage the For Each File task. I'm trying to think of a way to use this, but nothing is coming to mind that seem to take up a lot of overhead; or be a real PITA to utilize.
Suggestions? Ideas? Comments on any of the above ideas?
Regards,
Bob McC
If I understood correctly, try a ForEach container for each file type and set the file criteria of each container to only pick up that file type.. Inside the container, add the task(s) required to load one file type.
Make sense?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 3, 2011 at 8:14 am
Alvin Ramard (3/2/2011)
If I understood correctly, try a ForEach container for each file type and set the file criteria of each container to only pick up that file type.. Inside the container, add the task(s) required to load one file type.
Make sense?
Actually it does. Thanks for the suggestion, I think I'll give it a whirl. Just to restate... have one ForEachFile container for every possible file that I can receive. If the file isn't there the Container won't execute, and the fact that each ForEachFile will only ever execute one file at most is immaterial. I didn't think outside the box enough to think of a ForEachFile container being used on only one file. I like it.
March 3, 2011 at 8:18 am
BobMcC (3/3/2011)
Alvin Ramard (3/2/2011)
If I understood correctly, try a ForEach container for each file type and set the file criteria of each container to only pick up that file type.. Inside the container, add the task(s) required to load one file type.
Make sense?
Actually it does. Thanks for the suggestion, I think I'll give it a whirl. Just to restate... have one ForEachFile container for every possible file that I can receive. If the file isn't there the Container won't execute, and the fact that each ForEachFile will only ever execute one file at most is immaterial. I didn't think outside the box enough to think of a ForEachFile container being used on only one file. I like it.
Actually, if there is no file, the container itself executes, but the tasks inside the container will not, since the container did not find any files.
Good luck. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 3, 2011 at 8:24 am
Alvin Ramard (3/3/2011)
Actually, if there is no file, the container itself executes, but the tasks inside the container will not, since the container did not find any files.
Good luck. 🙂
Haha, I knew that, and specualted that I'd get corrected on it as soon as pressed <Post Quoted Reply>. But Yes I realize that the container itself did actually execute, and I can have a Success constraint following it. Thanks.
March 3, 2011 at 8:27 am
BobMcC (3/3/2011)
Alvin Ramard (3/3/2011)
Actually, if there is no file, the container itself executes, but the tasks inside the container will not, since the container did not find any files.
Good luck. 🙂
Haha, I knew that, and specualted that I'd get corrected on it as soon as pressed <Post Quoted Reply>. But Yes I realize that the container itself did actually execute, and I can have a Success constraint following it. Thanks.
😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply