Excel connection manager error in SSIS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Excel connection manager error in SSIS package

Author

Topic

kishore7
Starting Member

13 Posts

Posted - 2012-07-24 : 10:41:01

I am getting this error, when i schedule a job for the ssis package, but when i run it manually in my BIDS , its working fine,,,

I am using a excel connection manager, i guess its causing the error
Error code: 0xC0024108

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.

Please help me out of this..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 11:03:21

are you dynamically generating the connection manager? see what value its generating at runtime and make sure its as per valid connection string for excel as in below format

http://www.connectionstrings.com/excel

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kishore7
Starting Member

13 Posts

Posted - 2012-07-24 : 13:29:59

yes, generating it dynamically

And this is my connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\IVR\Prospect\BBKBNITProspect.xls;Extended Properties="EXCEL 4.0;HDR=YES";

This is how i have in my config file for excel connection manager....

<Configuration ConfiguredType="Property" Path="\Package.Connections[Excel_Connection_Manager].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>R:\IVR\Prospect\BBKBNITProspect.xls</ConfiguredValue>
</Configuration>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 14:44:15

Excel 4.0? which is excel version you're trying to connect to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kishore7
Starting Member

13 Posts

Posted - 2012-07-24 : 14:59:16

MS Excel 97-2003
--this is the type of file which i need to import

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 15:12:03

then it should be Excel 8.0

also make sure dynamic string its generating is having same format of conection string. Put a breakpoint and check value for variable holding connection string value before you execute the export task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kishore7
Starting Member

13 Posts

Posted - 2012-07-25 : 10:40:55

Now i getting a new error regarding password

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specific state". you may not be authorized to access this information. this error occurs when there is a crytographic error. Verify that correct key is available.

and my protectionlevel is set to Encryptsensitivewithuserkey in the package

Thanks..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 11:18:00

quote:
Originally posted by kishore7

Now i getting a new error regarding password

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specific state". you may not be authorized to access this information. this error occurs when there is a crytographic error. Verify that correct key is available.

and my protectionlevel is set to Encryptsensitivewithuserkey in the package

Thanks..

and are you using same account to run the package?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kishore7
Starting Member

13 Posts

Posted - 2012-07-25 : 15:13:28

yes, I am able to run my package succesfully in BIDS, but when i schedule job its failng, and giving this error

Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specific state". you may not be authorized to access this information. this error occurs when there is a crytographic error. Verify that correct key is available.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 16:48:16

in that case is the job configured to run with same account? if not it wont work. If you really want to configure package running from job why not choose EncryptSensitivewithPassword and set same password for it while adding to job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kishore7
Starting Member

13 Posts

Posted - 2012-07-25 : 17:14:20

Thanks!!!!

Got it working now

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 17:18:07

cool...Encryptsensitivewithuserkey relies upon useraccount so unless you use same account to run package it wont be able to execute as its sensitive properties will be unavailable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources