November 8, 2011 at 11:28 am
I get an error when attempting to Dynamic set the Connection String to an Excel OLE DB DEstination Connection.
I can do this when going to an Excel Destination without any problem.
This is the error that I get:
The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Any help would be greatly appreciated.
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 8, 2011 at 11:37 am
Is the connection string blank/incomplete at validation-time, and populated by a variable at runtime?
If so, have you set Delayed Validation on the package?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 11:55 am
Don't set the connection string property. Set the ExcelFilePath property. I find that it causes fewer headaches than trying to build a string for the connectionstring, since unlike the other connectors, the Excel connection string has several parameters that you can use, only one of which is the actual file path.
November 8, 2011 at 12:15 pm
I hope that this helps but the Connection String Property of the Destination Connection Manager is set to:
DataSource=C:\SSIS\TXQuoteActivity\TXQuoteActivity.xlsm;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;
I believe that it may have something to do with the extended properties or the fact that it is an OLEDB COnnection.
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 8, 2011 at 12:30 pm
Did you try leaving the connection string as it is, and changing the excelfilepath instead?
To make sure everything goes properly, delete the existing one you have. Create a new one, select the file you want to use. Then when everything looks good, change the ExcelFilePath, to the correct file path, and set DelayValidation to true if you need to.
November 8, 2011 at 12:31 pm
kramaswamy (11/8/2011)
Don't set the connection string property. Set the ExcelFilePath property. I find that it causes fewer headaches than trying to build a string for the connectionstring, since unlike the other connectors, the Excel connection string has several parameters that you can use, only one of which is the actual file path.
I have set the Excel Path property when using Excel as a Destination and that is relatively straight forward but I'm using an OLEDB Destination Task (Excel 12.0).
I must be missing something?:w00t:
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 8, 2011 at 12:34 pm
Ah sorry, didn't read that carefully enough. Okay then, ignore me, I've never used that connector before 😛
November 8, 2011 at 12:44 pm
Try this format for the connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SSIS\TXQuoteActivity\TXQuoteActivity.xlsm;Extended Properties="Excel 12.0 Xml;HDR=YES";
(You have the file extension as "xlsm". I'm used to "xlsx". Is that a typo, or is the file actually named that? Either way, I copied it into this string with it from your post.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 12:48 pm
An Excel File with an .xlsm is an Excel File that is Macro Enabled.
I'm not used to it either, until now. 🙂
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 8, 2011 at 12:50 pm
I checked, and the connection string for xlsm files needs to be "Excel 12.0 Macro". Try that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 1:17 pm
My connections string has been working fine as Excel 12.0 as soon I changed it it corrupted my package.:w00t:
I don't know of a way to set the connection string via an expression.
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 8, 2011 at 1:30 pm
I usually go to properties of the connection, click on the "..." button next to Expressions, select Connection String from the drop-down, build the expression.
I'm assuming you've tried that already, so maybe I'm misunderstanding what you're trying to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 9, 2011 at 8:37 am
GSquared (11/8/2011)
Try this format for the connection string:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SSIS\TXQuoteActivity\TXQuoteActivity.xlsm;Extended Properties="Excel 12.0 Xml;HDR=YES";
(You have the file extension as "xlsm". I'm used to "xlsx". Is that a typo, or is the file actually named that? Either way, I copied it into this string with it from your post.)
Perhaps I misunderstood you?
In the Properties of the Connection I specify the following variables:
@[Provider] + @[DataSource] + @[ExtendedProperties]
The Expression Evaluates to:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SSIS\TXQuoteActivity\TXQuoteActivity.xlsm;Extended Properties="Excel 12.0 Xml;HDR=YES";
As soon as I do this I get cannot acquire connection from the connection manager.
After I remove the expression and I have to respecify the path of the file in the Connection Manager.
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 11, 2011 at 6:25 am
Per what I read, where the connection string says "Xml", it needs to instead say "Macro" for .xlsm files. Try changing just that.
I haven't tried to import from that kind of file, so I'm not certain this will work. I just know I've been able to import from variable xlsx files this way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2011 at 10:03 am
On reading this blog I noticed a lot of confusion, so as I found myself having to do this today I thought I might have a go at making it a bit clearer as to how to point SSIS Connection Manager to an excel 2007 .xlsm file.
First, create your connection manager in OLEDB
Right mouse click in connection Managers and select
New OLE DB Connection
In the Configure OLE DB Connection Manager Window select
New...
In the Connection Manager Window select the Provider Dropdown as
Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider
Don’t fill anything else out and select OK and OK again to exit the Connection Manager.
You should now have a Connection Manager called
Data Connection
Double click this to enter the edit mode again.
In the Panel to the left select
ALL.
Scroll to the top of the window to the advanced section.
In extended Properties enter entry add
Excel 12.0
Scroll down to the Source Section and select Data Source
and enter the path and file name of the Excel file you wish to connect to.
Click out of the Cell and then select Test Connection.
You should get
Test Connection Succeeded.
To use this connection in SSIS, create a Data Flow Task and click into it.
Select
OLE DB Source from the Data Flow Sources. Do not use Excel Source.
Edit the OLE DB Source and select our Data Connection Manager as the
OLE DB Connection Manager.
Select the Data access mode as Table or view
Select the TAB page for the Excel Spreadsheet
Check the columns are as expected.
You are now connected to an Excel 2007 .xlsm file in SSIS.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply