| Author |
Topic |
|
dhani
Posting Yak Master
132 Posts |
 Posted - 2008-10-10 : 19:16:35
|
| Hello All,i have a simple SSIS package(which loads data from flat file to sql server table)till now the flat file name is same everytime,now as per the requirement, we will get files with different names (but extensions of the file is always same)example: customer data file may name as cus36929.ctd or c290003.ctd or 7283738.ctd or any.ctd (here .ctd is extension but it is text file) so based on extension it has to take the flat file source,how can i automate it rather than everytime select the file and run the packagecan any one please help meThanks in adavnceasini |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-10 : 20:09:41
|
| Use ForEachLoop container and use all your data flow task inside it creating variables which will loop everytime it sees that extension. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-11 : 02:08:56
|
| or even use For Each File Enumerator available in For Each loop and give the folder containing files as source. it will loop through files inside folder and process them. then inside container have data flow task as sodeep suggested to do processing. you can get file names inside a variable inside container for each iteration and check if extension is .ctd and proceed only if its true by using conditional precedence constraint. |
|
|
|
dhani
Posting Yak Master
132 Posts |
Posted - 2008-10-13 : 12:47:28
|
quote:
Originally posted by visakh16
you can get file names inside a variable inside container for each iteration and check if extension is .ctd and proceed only if its true by using conditional precedence constraint.
Hello visakh,i got it. finally it is worked, but here i have a small doubt, as in your response, it says below quote:
you can get file names inside a variable inside container for each iteration and check if extension is .ctd and proceed only if its true by using conditional precedence constraint.
actuall what i did was just assigned the folder location, then file extension that's it, how can i do it programatically (as u suggested in above quote)once again Thank you to both of you(Sodeep, Visakh) for your good responceBest Regardsasini |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 12:58:28
|
| programmatically you can do it by getting the filename inside foreachloop by mean of script task and storing it in a variable. then in precedence constraint linking to next task add an expression which checks if extension id .ctd (or even this can come from a variable). only id its true it will execute rest of tasks inside loop, else it will continue with nextr iteration (next file) |
|
|
|
dhani
Posting Yak Master
132 Posts |
Posted - 2008-10-13 : 13:23:24
|
| hi Visakh,thank you for immediate responsecan you please assist me some morepresently i completed 1 task (i mean from one source file(based on extension) it loads to sql server tablesame wise i have to do 10 more tasks, source files (same based on extension) it loads to the tables in sql servercan you please let me know what is the best process.once again thank you for your quick responseBest Regardsaswani |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-13 : 13:54:19
|
| Foreach Loop container will see any files with that extension in the folder and process. You can setup a job with SQL Server Agent which will automate your process . |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-13 : 14:01:39
|
quote:
Originally posted by dhani
hi Visakh,thank you for immediate responsecan you please assist me some morepresently i completed 1 task (i mean from one source file(based on extension) it loads to sql server tablesame wise i have to do 10 more tasks, source files (same based on extension) it loads to the tables in sql servercan you please let me know what is the best process.once again thank you for your quick responseBest Regardsaswani
are the structure of all files & destination tables same? |
|
|
|
dhani
Posting Yak Master
132 Posts |
Posted - 2008-10-13 : 14:20:10
|
| [/quote]are the structure of all files & destination tables same?[/quote]Hi Visakh,yes almost all files were same (flat files). tables also same. but i might need to use some derived columns (for some tables need to add some columns to flat file columns, thse new columns were just stright forward, i mean not from any other source these are just some null values)Best RegardsasiniBest Regardsasini |
|
|
|
|
how to automate source file selection,by extension