January 15, 2013 at 4:18 am
Hi, I was to import excel data into a table in SQL Server,regularly on a scheduled time.
I was able to import data from a "particular excel" into DB using Import-Export Wizard (SSIS package), executing it at scheduled time via SQL Server Agent job scheduler, perfectly.
But, now I came to know that.. the user will daily pull out an Excel Report ( perhaps with same Name+current-date as the file name) and drop that into the shared folder. Now the SQLServer Agent Scheduler ought to pick the latest excel and kindly append the data of only that file into the particular table of the database.
My problem is while creating the SSIS package in wizard I will necessarily be giving - one particular excel name in the DataSource. Now how can I make it pick the latest excel in the folder and put it in the database. :Whistling:
Any solution that you can suggest?
January 15, 2013 at 1:28 pm
You'll have to modify the package in BIDS and put an expression into the ExcelFilepath property in your connection to dynamically change the filepath at runtime. The below expression will give you "C:\MyFolder\MyExcelYYYYMMDD.xlsx"
"C:\\MyFolder\\MyExcel" + (DT_WSTR, 4) YEAR( GETDATE() )+ RIGHT("0"+(DT_WSTR, 2) MONTH( GETDATE() ),2)+ RIGHT("0"+(DT_WSTR, 2) DAY( GETDATE() ),2) + ".xlsx"
if you're not familiar with expressions I'd recommend getting a beginners SSIS book.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
January 15, 2013 at 7:53 pm
I have seen a system where it would look at a processed directory and process all of the files in that directory, when the package had finished with a file it moved it to a new directory. Also if there was an error with the files it would then move it to an exception directory.
To do this you will not be able to use the default templates and wizards, you will have to custom work the SSIS packages.
January 16, 2013 at 6:59 am
Mykre (1/15/2013)
I have seen a system where it would look at a processed directory and process all of the files in that directory, when the package had finished with a file it moved it to a new directory. Also if there was an error with the files it would then move it to an exception directory.To do this you will not be able to use the default templates and wizards, you will have to custom work the SSIS packages.
You can start with the template and modify the package in BIDS. To move the files based on the results you just have to add the File System Task to move the file to another location.
You can also add a 3rd party File watcher task that will constantly look for a specific file and run the process when someone dumps a file in a location. Konesans had a free file watcher task that I used to use at my old job.
Either way you'll need to be familiar enough with BIDS and SSIS to program the package.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply