November 11, 2013 at 3:44 am
Hi, I'm learning SSIS and as a part of my project here is one scenario:
I've a folder location where .xls file will come once on daily basis and the name will consist of date (example MyData20131110.xls, MyData20131111.xls etc). Now I want to move the data to my table from the xls with maximum date (say xls file name be MyData+max(date)) on daily basis. I am able to do perform one time normal data movement. But how to configure it to run on daily basis ?
November 11, 2013 at 3:50 am
You'll have to create a SQL Job to do that.
In SQL Server Management Studio connect to your SQL Server engine, go to "SQL Server Agent" (You many need to enable that) and then create a new job with an "Integration Services Package" step in it.
It's pretty self explanatory once you know where to look 🙂
November 11, 2013 at 4:16 am
What my requirement is that today i have just one file with name MyData+<AnyDate>.xls. But there will be one file added on daily basis and I want to pick that file (The older file needs to be there for some days for some reasons).
November 11, 2013 at 4:21 am
To achieve this you should set the "ExcelFilePath" and "ExcelServerName" properties of your excel connection as expressions.
November 11, 2013 at 6:11 am
A little more information will be helpful. 🙂
November 11, 2013 at 6:25 am
sqlnaive (11/11/2013)
A little more information will be helpful. 🙂
Well, it's your problem; you tell us what you don't understand.
I think you will need a script task to determine which one in the folder is the most recent. Either that or move each file out of the folder as soon as it's been processed.
John
November 11, 2013 at 7:22 am
sqlnaive (11/11/2013)
A little more information will be helpful. 🙂
If your question is about "How to have a dynamic excel source", a quick google search returns this:
setting up an ssis package with a dymanic excel source
if it's something else then, as John stated, be more specific.
November 11, 2013 at 11:27 pm
Yes, I'm looking to pickup the excel file dynamically based on the name (having date added as string to the name). Let me try and get back. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply