cannot use "dynamic" Connection String with Excel?
-
Hi, Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: 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.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.1. DelayValidation is set true. 2. Excel Connection String is set as
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\folde\Data\Book2.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
that i store in variable please advice. thanks, Vinny
-
Hi, Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: 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.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.1. DelayValidation is set true. 2. Excel Connection String is set as
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\folde\Data\Book2.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
that i store in variable please advice. thanks, Vinny
Does the file "D:\folde\Data\Book2.xlsx" exist?
-
Does the file "D:\folde\Data\Book2.xlsx" exist?
yes, the file are exist
-
Hi, Im trying to make a ssis to load data from dynamic excel file that store in the folder. i've try every totorial but it still give me the error like this
[Connection manager "Excel Connection Manager"] Error: 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.
Error: The result of the expression "@[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.1. DelayValidation is set true. 2. Excel Connection String is set as
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\folde\Data\Book2.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";
that i store in variable please advice. thanks, Vinny
Just a thought. Maybe the connection string parser is being upset by the
=inside the extended properties string. Can you try withoutHDR=YESjust to see if that is the problem? Cheers, PeterSoftware rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
-
Just a thought. Maybe the connection string parser is being upset by the
=inside the extended properties string. Can you try withoutHDR=YESjust to see if that is the problem? Cheers, PeterSoftware rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
its give me another error Error 3 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. ---> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0 but, when i put HDR=YES the error was change. it says neesd new metadata, its seem like i have different format of file. event thought the file was the same.
-
its give me another error Error 3 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. ---> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) --- End of inner exception stack trace --- at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0 but, when i put HDR=YES the error was change. it says neesd new metadata, its seem like i have different format of file. event thought the file was the same.
It is very difficult to guess what is going on without more information about the actual code that causes the exception. Please show the code where the error occurs, indicating the exact line that raises the exception, and the exact content of all variables that are being used.