September 26, 2014 at 10:25 am
Because of the many problems with SSIS and trying to import data into a SQL Server table from Excel, I have written a very simple Access database to copy the data from Excel into Access, then copy it from the Access table into SQL Server. I tried doing it without the Access table in the middle and that does not seem to work very well, but with the Access table in the middle, it works every time. Now I would like to have this work every night within an SSIS package, or even just within a Stored Procedure.
Any suggestions?
Thanks
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
September 26, 2014 at 10:45 am
Because of the many problems with SSIS and trying to import data into a SQL Server table from Excel
Not to sure why you think it is that big of a problem...but everyone is entitled to their opinions.
I have written a very simple Access database to copy the data from Excel into Access, then copy it from the Access table into SQL Server
If it is pretty simple and depending on how much data and complexity you are talking about it, would have probably been easier to just use OPENROWSET and query the data directly from T-SQL.
I tried doing it without the Access table in the middle and that does not seem to work very well, but with the Access table in the middle, it works every time. Now I would like to have this work every night within an SSIS package, or even just within a Stored Procedure.
I think you are overcomplicating it and it will be something harder to maintain. Moving data from Excel to Access with SSIS is the same as moving data from Excel to SQL Server, maybe with less data manipulation required.
Access you can utilize linked tables to insert into SQL Server tables. You could build out a macro to move data from Excel to Access to SQL Server. Then call that macro from a batch file within your SSIS package or straight from SQL Agent job, using Access command line.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 26, 2014 at 10:56 am
What issues are you experiencing with importing from Excel?
Unless you are correcting data as it moves from Excel to Access, I cannot think of any reason why you could not use SSIS to import the data from Excel.
If you used the import wizard to create the package for you, then try to use it as is, then yes you might have problems. Chances are you'll have to tweak a few items and the it should work every time, as long as the format of your spreadsheet(s) doesn't change.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 30, 2014 at 5:19 am
Unless you are correcting data as it moves from Excel to Access, I cannot think of any reason why you could not use SSIS to import the data from Excel.
Seriously? How about problems with data-type guesswork which the Excel driver kindly attempts?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply