February 9, 2012 at 12:13 am
Drew Gillson (2/8/2012)
I just experienced this issue, and although I was not able to get SSIS to talk to an Excel file directly on my 64-bit Windows Server OS (ACE 12.0 not registered errors), I was able to execute a SELECT * FROM OPENROWSET just fine. Therefore, I would propose a good work-around for this issue is to just create a view and then use an OLEDB source from within SSIS instead.Something like this should do you fine:
CREATE VIEW [dbo].[view_OurWarehouseInventory] AS
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Data\Shared\WarehouseInventory.xlsx', [Inventory$])
GO
By the way, I hate SSIS. It is a bizarre application and every time I use it I feel like I'm poking my own eyes out with splintery chopsticks.
I guess the reason it didn't work is because BIDS is a 32-bit application.
ps: I love SSIS 😀 Most of the time people hate it because they don't know enough of it and spend too much time wasting on little quirks. But hey, you don't hear me complaining, it's what gets me consulting jobs 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 13, 2012 at 12:16 pm
Hope you can resolve my problem.....
Requirement : Using SSIS to export data from SQL tables to ".xlsx"
Specifications:
Windows Server 2003 R2 Enterprise x64 bit , Service Pack 2
SQL SERVER 2008 R2 Management Studio
Visual Studio 2008
As it is server there are no Ms Office products installed. Except microsoft office 2003 web components.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Dties="Excel 12.0 XML;HDR=YES";
Insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0'," & _
"'Excel 12.0 XML;Database=" + excelFilePath + "','SELECT " + ColumnNames + " FROM [" + ExcelTable + "$]') SELECT " + ColumnNames + " FROM [" + TableName + "]
Two scenarios i tried.... in both cases i get errors.
First scenario
When I installed AccessDatabaseEngine_64,
and executed package
I get error message "Excel Connection Manager. Connection may not be configuredcorrectly or you may not have the right permission on tis connection"
In this case "filename.xlsx" is not created.
Second Scenario
When I un- installed AccessDatabaseEngine_64 and installed AccessDatabaseEngine,
I get error message " Microsoft.jet.oledb is not registered". But, I used 'Microsoft.ACE.OLEDB.12.0'...
In this case "filename.xlsx" is created.
February 13, 2012 at 1:23 pm
For starters, your Connection string under the Excel connection manager should look more like this:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=drive letter:\your file name here.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
The above should have been automatically generated by creating an Excel connection manager and selecting your file.
The other piece that you posted seems a bit odd. You need to do the following to have this work:
1) Create a Data Flow Task
2) Inside the Data Flow Task create an ADO NET Source (you can use OLE DB Source instead but it requires data conversions for varchars)
3) Open the ADO NET Source, set it to connect to your database connection manager (you should already have one or you can create one here), set your data access mode to SQL command, and enter your sql statement below
4) Inside the Data Flow Task create an Excel Destination
5) Open the Excel Destination, select your Excel connection manager (it's probably auto-selected already), select "Table or View" for the Data access mode, and then simply select the sheet name that you want to insert into
When looking at the Excel Destination properties, you would see that AccessMode is set to OpenRowset and OpenRowset is set to 'Your sheetname$'. You wouldn't see a whole insert/select statement here nor, is one necessary.
As for you scenarios, last time I checked, the 64 bit engine was a dud, so don't use it. Besides, BIDS would use the 32 bit drivers anyway. Only SQL Agent would use the 64 bit driver if you run the package under a SQL Agent job in 64 bit mode (default). Your second scenario should work with the instructions above. If not, then perhaps you just need to reboot the server.
Finally, I saw no functional difference between the 2007 driver and the 2010 driver. Perhaps, you might just want to install the 2007 driver.
________________________________________
Get some SQLey goodness at Dirt McStain's SQL Blog
June 24, 2013 at 8:58 am
FWIW, I just spent a few days debugging what I think is the exact same situation. It was a frustrating enough excercise that I thought I'd share my findings/solution.
The problem for us is that we execute packages not from a commandline or Agent job, but using c# and the "api" for SSIS (from the ManagedDTS assembly). So launching DTExec in a 32bit shell wasn't an option!
At first, I came across references that said to install the Access Database Engine, ostensibly to get the drivers needed:
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
We did that, but still our code failed. Finally, I was able to get an error message returned that led me to the following msdn page:
http://msdn.microsoft.com/en-us/library/cc280527(SQL.110).aspx
the important part being:
"If the 2007 Office system is not installed on the computer on which Integration Services is running, you have to install the provider separately. To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the components on this Web page, 2007 Office System Driver: Data Connectivity Components."
So, I followed the link to the Data Connectivity Components download and installed that on the server where our code and packages were being loaded and executed. And suddenly... success!
April 7, 2016 at 8:28 am
Drew Gillson (2/8/2012)
I just experienced this issue, and although I was not able to get SSIS to talk to an Excel file directly on my 64-bit Windows Server OS (ACE 12.0 not registered errors), I was able to execute a SELECT * FROM OPENROWSET just fine. Therefore, I would propose a good work-around for this issue is to just create a view and then use an OLEDB source from within SSIS instead.Something like this should do you fine:
CREATE VIEW [dbo].[view_OurWarehouseInventory] AS
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Data\Shared\WarehouseInventory.xlsx', [Inventory$])
GO
By the way, I hate SSIS. It is a bizarre application and every time I use it I feel like I'm poking my own eyes out with splintery chopsticks.
You rock! This saved me from all the BS with SSIS.
Cheers
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply