May 25, 2012 at 7:47 am
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?
May 25, 2012 at 7:58 am
Dear Wendy,
Pretty easy. You have to make a dataflow in the package design. Let me know if you have BIDS installed? and then I will give you a step by step procedure. Its a simple retrieval from flat file source, then column mapping and finally insertion in the specific table of the database you want. If you need to do it for multiple files, you can always use a foreach loop container in the control flow.
Just ping me and I will reply.
Regards,
Rafayz
May 28, 2012 at 7:33 am
Hi Wendy,
I'm not sure if it's the same for 2008 as for 2005 (what I'm using) but you may need to use an expression in the excel source to accommodate the changing date part of the file name every day. You could set the expression for ConnectionString to to
"C:\Data\Filename"+(DT_WSTR,2) DAY(GETDATE())
+(DT_WSTR,2) MONTH(GETDATE())
+(DT_WSTR,4) YEAR(GETDATE())
+".xls"
i.e. for today it evaluates to C:\Data\Filename2852012.xls
That way your file connection would always be pointing to the correct file.
🙂
Adrian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply