<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Import Excel data into DB automatically...
Import Excel data into DB automatically using scheduler...but DataSource excel changes daily
Rate Topic
Display Mode
Topic Options
Author
Message
haimantadawar
haimantadawar
Posted Tuesday, January 15, 2013 4:18 AM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, January 19, 2013 5:01 AM
Points: 2,
Visits: 9
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.
Any solution that you can suggest?
Post #1407149
Want a cool Sig
Want a cool Sig
Posted Tuesday, January 15, 2013 1:28 PM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221,
Visits: 452
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407465
Mykre
Mykre
Posted Tuesday, January 15, 2013 7:53 PM
Forum Newbie
Group: General Forum Members
Last Login: Friday, April 12, 2013 6:35 PM
Points: 3,
Visits: 134
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.
Post #1407549
Want a cool Sig
Want a cool Sig
Posted Wednesday, January 16, 2013 6:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221,
Visits: 452
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 - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1407819
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
