November 11, 2011 at 12:24 pm
I created an Excel Connection but I can't select the sheet using Excel Connection Manager.
It generates SQL to create a table.
This is different than the OLE DB Office 12.0 Provider.
Also you can't specify a Macro Enabled Excel Format.
Any thoughts would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2011 at 8:35 pm
Not trying to sell software because I get nothing out of it, but if you need to do anything complex with excel use cozyroc.
November 12, 2011 at 9:58 am
Thanks.
I'm dealing with 64 bit machines which could be a big part of my problem.
I noticed that CozyRoc has an Excel Connection
Manager that is supportive of 64 bit Operating System.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 12, 2011 at 10:07 am
I think the jet engine is only 32 bit, so you would need to drop the package down to 32 bit if you wanted to use the jet engine.
November 12, 2011 at 10:13 am
Do you mean set the 64 Bit property to False on the Connection or what?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 12, 2011 at 10:16 am
Nope, property of the package:
http://msdn.microsoft.com/en-us/library/ms141766.aspx
If you want to add this as to a sql agent job, be aware this property only applies to debugging in the package.
There is a 64 bit engine for excel, its just not the jet engine.
November 12, 2011 at 10:34 am
Found answer, post removed.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 12, 2011 at 10:59 am
mark 38539 (11/11/2011)
Not trying to sell software because I get nothing out of it, but if you need to do anything complex with excel use cozyroc.
What I'm talkig about doing is simple when you select the Excel Destination for Excel 97-2003.
I don't see why it has to be so difficult with Excel 2007 & above. :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 12, 2011 at 2:44 pm
To quote the developer of cozyroc "the only easy part of excel is the end user experience." couldn't have said it better myself. But go ahead and post your resolution for others, did you switch to a different provider?
November 12, 2011 at 3:41 pm
I did not come up with a solution, I had a question another question that was not important.
I believe that the best solution is to use Excel in this case.
Currently there are a 7 workbooks that get populated from External Data Sources and 8 Pivot tables that get populated from other worksheets by the use of some very complicated macros..
I'm going to eliminate most of the macros and use external data sources to populate the Pivot Tables, etc.
It is a lot easier to use Excel and the formatting is superior.
It is a lot less effort than SSIS in this case.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply