November 17, 2011 at 10:34 am
Hi All,
I have built a package using SSIS and used variables for File-Names,Folder Paths, File Paths, dates and several folder paths.
I have tested this and it works fine for the 'one' file/folder path types.
I want to re-use this package to import similar files. Could you please tell me of the best ways to do this. I would be grateful if you could point me to some online resource or book which has an example of this.
Many thanks
November 17, 2011 at 4:23 pm
Sounds more or less like this: http://qa.sqlservercentral.com/Forums/Topic1202577-364-1.aspx
It depends on what you are trying to do.
Do you want to load a few files occasionally, or a large amount of files regularly?
Do you want to develop an in depth solution or a quick fix?
Are all your files on the same server?
Are all your files in the same structure with the same datatypes?
Essentially you've got yourself a package template.
You could simply execute the package however many times you like from the command line, overwriting the variable values each time.
You could use your template to create a number of packages, one for each file load.
You could create a table that contains the values for a set of executions and run through them with a foreach loop (either in a parent/child package structure, or internally within a single package).
November 18, 2011 at 2:14 am
Hi Sam,
Many thanks for your reply. The answers to your questions are as follows:
1. Do you want to load a few files occasionally, or a large amount of files regularly?
The files will be loaded every month and occassionally on days.
2. Do you want to develop an in depth solution or a quick fix?
I would be happy either way as long as it works consistently.
3. Are all your files on the same server?
Yes, they are on the same server.
4. Are all your files in the same structure with the same datatypes?
--The package I have built built takes care of the formating
Essentially you've got yourself a package template.
You could simply execute the package however many times you like from the command line, overwriting the variable values each time.
--This sounds like an option. You could use your template to create a number of packages, one for each file load.
-- I am not looking to do this.
You could create a table that contains the values for a set of executions and run through them with a foreach loop (either in a parent/child package structure, or internally within a single package). --- This is my prefererred method. Could you please advise me of any examples which you know that exists?
November 20, 2011 at 4:51 pm
I'm not sure that there's much in the way of examples out there. There are a few examples of ETL framework but they aren't quite what you are after and are much more complex, though you could base what you are trying to do off of one of them.
Try the instructions by chris_hurley in the following thread, they should approximate what you are after.
http://qa.sqlservercentral.com/Forums/Topic1202577-364-1.aspx
Start with an execute sql task that queries your table. Set result set property to Full result set;
Map the result to a variable of type object;
Then have a for each loop set to run for each ADO Enumerator and select said variable.
Set up further variables for each field returned in the initial table query and map them in the for each loop.
What you've essentially got now inside your for each loop is a process that will loop through each of your records in the table holding the connection data. Use your variables to then manage your connections.
Poast back if you require more detail, or would like ETL framework examples
November 21, 2011 at 4:08 am
First of all many thanks for your support on this.
I have since tried to simplify my model as much as possible.
I have 7 distinct individual folders, I have created a variable for a 'FolderName' in the package. I now need a method to loop through the folder names and run the package for each of the folders individually.
1. I am not sure how to proceed after this point. Should I be using a Parent-Child package?
2. If I create a parent package with the variable values in a package configuration, how would I loop through these values and pass the values to the Child package and run the package for each of the values one after the other?
November 21, 2011 at 4:32 am
Read about Package Configurations. If you have all your changeable properties in variables, all you have to do is set the value of those variables in your configuration file or table. Then, when you want to run it in a different environment, just use a different configuration file or update the configuration table. That's the simplest way, and it means that when you want to change your package in future, you only have to change it once, and not once for each environment.
John
November 21, 2011 at 4:34 pm
aarionsql (11/21/2011)
First of all many thanks for your support on this.I have since tried to simplify my model as much as possible.
I have 7 distinct individual folders, I have created a variable for a 'FolderName' in the package. I now need a method to loop through the folder names and run the package for each of the folders individually.
1. I am not sure how to proceed after this point. Should I be using a Parent-Child package?
2. If I create a parent package with the variable values in a package configuration, how would I loop through these values and pass the values to the Child package and run the package for each of the values one after the other?
I wouldn't worry about Parent-Child at this point.
I think I have a better idea; see if this does what you are after:
http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply