Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jassie
Constraint Violating Yak Guru
332 Posts |
 Posted - 2012-05-25 : 09:48:28
|
| I am asking this question since I have not worked with ssis packages yet. Basically I would like to automate the following steps and what like recommendations on how to have the package and run the SSIS package again.In a sql server 2008 r2 professional edition database, I receive a daily file from the customer in an excel 2003 spreadsheet where I always need to obtain data from the fourth tab. The excel files I receive from the customer have the same name except the last part of the name includes the date of the file. I then use the import wizard to point the excel spreadsheet to a table in a specific database.Thus how would you setup an SSIS package to accomplish this task? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-25 : 10:21:07
|
| Create a SSIS package with data flow task inside for each loop. The for each loop will be configured to use file enumerator and it will be pointed to shared folder where you get file from customer (if file comes in any other way make sure you dump it onto shared folder prior to this).There would be variable created and configured inside for each loop to get filename it found during each iteration.To make the source excel name dynamic, you can add a variable inside ssis and get current date inside it using now() function. then add a script task inside foreachloop and add a logic to look for current date pattern in filename. Return a boolen variable value based on whether pattern is found or not. The data flow task will be linked from script task by means of precedence constraint based on expression where you check if boolean variable is true. The data flow task will have excel source which will point to your excel file by means of expression which points to variable where you store filename from for each loop . You will also have oledb destination to point to your sql server table. You can specify the tab name inside excel source to always look for the fourth tab.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
ssis package setup