| Groups | Blog | Home |
sql server dts : Loop through Excel Files and Tables
spreadsheets located in a specific folder and followed the instructions in the MSDN publication 345182 - How to: Loop through Excel Files and Tables. The instructions lead to a corrupt package with the following error message that I cannot track anywhere: 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. Evidently, the variable to provide the path is not getting to where it needs to go. Has anybody been successful getting these instructions to work - I am wondering if there is a typo somewhere. I have spent many hours on this and
Since I could not get the first example to work, I switched to the second:
To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator. I set up the Microsoft Jet OLE DB Provider, but cannot select it with any data flow source except DataReader. Then I was blocked in that this source is expecting a SQL statement. Can someone that has successfully implemented the first or the second scenario for this tell me what the missing instructions are. [quoted text, click to view] "bhorwatt" wrote:
> I have set up a simple SSIS package to loop through several Excel > spreadsheets located in a specific folder and followed the instructions in > the MSDN publication 345182 - How to: Loop through Excel Files and Tables. > The instructions lead to a corrupt package with the following error message > that I cannot track anywhere: 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. Evidently, the variable to provide > the path is not getting to where it needs to go. > > Has anybody been successful getting these instructions to work - I am > wondering if there is a typo somewhere. I have spent many hours on this and
So you loop through Excel files and set the location of the file through
property Expressions? The property you need to set is the ExcelFilePath property. It sounds as though you are setting ConnectionString. -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] "bhorwatt" <bhorwatt@discussions.microsoft.com> wrote in message
news:BA1E36C3-D370-447B-B4BE-C91F68FFABF6@microsoft.com: > Since I could not get the first example to work, I switched to the second: > To loop through Excel tables by using the Foreach ADO.NET Schema Rowset > enumerator. I set up the Microsoft Jet OLE DB Provider, but cannot select it > with any data flow source except DataReader. Then I was blocked in that this > source is expecting a SQL statement. Can someone that has successfully > implemented the first or the second scenario for this tell me what the > missing instructions are. > > "bhorwatt" wrote: > > > I have set up a simple SSIS package to loop through several Excel > > spreadsheets located in a specific folder and followed the instructions in > > the MSDN publication 345182 - How to: Loop through Excel Files and Tables. > > The instructions lead to a corrupt package with the following error message > > that I cannot track anywhere: 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. Evidently, the variable to provide > > the path is not getting to where it needs to go. > > > > Has anybody been successful getting these instructions to work - I am > > wondering if there is a typo somewhere. I have spent many hours on this and > > am about to give up. I am using SQL Server 2005 SP1.
Per your answer, I altered step 9 of the MSDN Article "How to: Loop through
Excel Files and Tables" from "select the ConnectionString property" to "select the ExcelFilePath property". Now I get the following error message: Error at Package1[Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property. Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have tried multiple ways to put quotes around the semicolon, but then it fails the check in the Property Expressions Editor. It passes through the check with the above code, but fails when trying to execute the package. Any further assistance you could provide would be appreciated. [quoted text, click to view] "Allan Mitchell" wrote:
> So you loop through Excel files and set the location of the file through > property Expressions? > > The property you need to set is the ExcelFilePath property. It sounds > as though you are setting ConnectionString. > > -- > > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > > "bhorwatt" <bhorwatt@discussions.microsoft.com> wrote in message > news:BA1E36C3-D370-447B-B4BE-C91F68FFABF6@microsoft.com: > > > Since I could not get the first example to work, I switched to the second: > > To loop through Excel tables by using the Foreach ADO.NET Schema Rowset > > enumerator. I set up the Microsoft Jet OLE DB Provider, but cannot select it > > with any data flow source except DataReader. Then I was blocked in that this > > source is expecting a SQL statement. Can someone that has successfully > > implemented the first or the second scenario for this tell me what the > > missing instructions are. > > > > "bhorwatt" wrote: > > > > > I have set up a simple SSIS package to loop through several Excel > > > spreadsheets located in a specific folder and followed the instructions in > > > the MSDN publication 345182 - How to: Loop through Excel Files and Tables. > > > The instructions lead to a corrupt package with the following error message > > > that I cannot track anywhere: 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. Evidently, the variable to provide > > > the path is not getting to where it needs to go. > > > > > > Has anybody been successful getting these instructions to work - I am > > > wondering if there is a typo somewhere. I have spent many hours on this and > > > am about to give up. I am using SQL Server 2005 SP1. >
Why are you using that string. There is a single property on the Excel
Connection Manager of ExcelFilePath which you set to your variable value and that is it. -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] "bhorwatt" <bhorwatt@discussions.microsoft.com> wrote in message
news:8547EE6F-1018-4F27-9286-C68F80C41A21@microsoft.com: > Per your answer, I altered step 9 of the MSDN Article "How to: Loop through > Excel Files and Tables" from "select the ConnectionString property" to > "select the ExcelFilePath property". Now I get the following error message: > > Error at Package1[Connection manager "SourceConnectionExcel"]: The > connection string components cannot contain unquoted semicolons. If the value > must contain a semicolon, enclose the entire value in quotes. This error > occurs when values in the connection string contain unquoted semicolons, such > as the InitialCatalog property. > > Error at Package1: The result of the expression > ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelFile] + > ";Extended Properties=\"" + @[User::ExtProperties] + "\""" on property > "ExcelFilePath" cannot be written to the property. The expression was > evaluated, but cannot be set on the property. > > I have tried multiple ways to put quotes around the semicolon, but then it > fails the check in the Property Expressions Editor. It passes through the > check with the above code, but fails when trying to execute the package. > > Any further assistance you could provide would be appreciated. > > > > "Allan Mitchell" wrote: > > > So you loop through Excel files and set the location of the file through > > property Expressions? > > > > The property you need to set is the ExcelFilePath property. It sounds > > as though you are setting ConnectionString. > > > > -- > > > > > > Allan Mitchell > > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > > http://www.konesans.com > > > > > > > > "bhorwatt" <bhorwatt@discussions.microsoft.com> wrote in message > > news:BA1E36C3-D370-447B-B4BE-C91F68FFABF6@microsoft.com: > > > > > Since I could not get the first example to work, I switched to the second: > > > To loop through Excel tables by using the Foreach ADO.NET Schema Rowset > > > enumerator. I set up the Microsoft Jet OLE DB Provider, but cannot select it > > > with any data flow source except DataReader. Then I was blocked in that this > > > source is expecting a SQL statement. Can someone that has successfully > > > implemented the first or the second scenario for this tell me what the > > > missing instructions are. > > > > > > "bhorwatt" wrote: > > > > > > > I have set up a simple SSIS package to loop through several Excel > > > > spreadsheets located in a specific folder and followed the instructions in > > > > the MSDN publication 345182 - How to: Loop through Excel Files and Tables. > > > > The instructions lead to a corrupt package with the following error message > > > > that I cannot track anywhere: 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. Evidently, the variable to provide > > > > the path is not getting to where it needs to go. > > > > > > > > Has anybody been successful getting these instructions to work - I am > > > > wondering if there is a typo somewhere. I have spent many hours on this and > > > > am about to give up. I am using SQL Server 2005 SP1. > > > >
That is the string provided in the referenced MSDN article if you are using
Connection String. I tried just using the variable and got the dreaded 'acquire' error as this person did in a posting on another site - I am in the same situation they are in and I plan to separate my package into another solution by itself to see if that works as that was a suggestion: What I'm trying to achieve is a SSIS package that will pick up 1 or more excel files, process the data in them via the conditional splitter, pushing the good data into a table, and all other rows into an error table. I'm having some issues using the ForEach container to process multiple excel spreadsheets into tables. The excel import into the tables is more or less working (imports data for good cases, but uses a null if the Excel Source when it gets an unexpected value - but that's a seperate problem). I found something related to this when searching, but it related to CTPs (June and September) and trying to reuse the connection strings they built up there (using my own variable names, naturally) causes a 'Property Value failure': -------------------------------------------------------------------------------- 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. -------------------------------------------------------------------------------- I attemtpted to use this: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::RankingFileFullPath] + ";Extended Properties=\"Excel 8.0;HDR=YES\";" The excel importer works fine as a stand-alone component. Trying to use the process defined in 'Profession SQL Server Integration Services' pp140, I tried to use an expression to assign the variable value to the connection string. I get a validation error: -------------------------------------------------------------------------------- Error at Import TPNB Ranking Excel spreadsheets [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error at Import TPNB Ranking Excel spreadsheets [DTS.Pipeline]: One or more component failed validation. Error at Import TPNB Ranking Excel spreadsheets: There were errors during task validation. Error at Excel Importer [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D. -------------------------------------------------------------------------------- Any advice? ..... ..... in addition .... I attempted to change the package - I set the Data Flow validation to Delay Validation, and changed the expression to change from: ConnectionString @[User::RankingFileFullPath] to ExcelFilePath @[User::RankingFileFullPath] This allowed the package to start debugging, and gave more information in the failure: -------------------------------------------------------------------------------------------- SSIS package "Excel Importer.dtsx" starting. SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Excel Importer' has been hit SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Foreach Loop Container' has been hit SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit Information: 0x4004300A at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Import TPNB Ranking Excel spreadsheets, ProductSalesRank Table [278]: Truncation may occur due to inserting data from data flow column "Rank" with a length of 1000 to database column "SalesRank" with a length of 50. Error: 0xC0202009 at Excel Importer, Connection manager "Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unrecognized database format 'D:\Testing\TestRanking.xls'.". Error: 0xC020801C at Import TPNB Ranking Excel spreadsheets, Excel Source [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Error: 0xC0047017 at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Import TPNB Ranking Excel spreadsheets, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Import TPNB Ranking Excel spreadsheets: There were errors during task validation. SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit Warning: 0x80019002 at Foreach Loop Container: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit SSIS breakpoint 'Break when the container receives the OnQueryCancel event' at executable 'Excel Importer' has been hit SSIS breakpoint 'Break when the container receives the OnWarning event' at executable 'Excel Importer' has been hit Warning: 0x80019002 at Excel Importer: The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS breakpoint 'Break when the container receives the OnPostExecute event' at executable 'Excel Importer' has been hit SSIS package "Excel Importer.dtsx" finished: Failure. The program '[2460] Excel Importer.dtsx: DTS' has exited with code 0 (0x0).-------------------------------------------------------------------------------------------- Panic, Chaos, Disorder ... my work here is done --unknown [quoted text, click to view] "Allan Mitchell" wrote:
> Why are you using that string. There is a single property on the Excel > Connection Manager of ExcelFilePath which you set to your variable value > and that is it. > > -- > > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > > "bhorwatt" <bhorwatt@discussions.microsoft.com> wrote in message > news:8547EE6F-1018-4F27-9286-C68F80C41A21@microsoft.com: >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |