Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts

Friday, 13 July 2012

Failed to decrypt protected XML node DTS:Password with error 0x8009000B

Unable to use a SQL job to execute a package designed in SSIS however it runs fine in Integration Services.  It reports the following error:
"Error Loading <>.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.".  You may not be authorized to access this information.  This error occurs when there is a cryptographic error.  Verify that the correct key is available "

Problem was due to the username set against the SQL Server Agent being different to the username used to designed the SSIS package.  To resolve this we left the Package default protectionlevel to "EncryptSensitiveWithUserKey".  When adding the SQL Server agent job we should set the protection level to be "Rely on Server Storage and roles for access control"

The following website was an excellent resource and help me understand the error:

http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/

Tuesday, 3 July 2012

SSIS - Error Column "XXX" Cannot convert between unicode and non-unicode string data types

When building an SQL Server Integrastion Services package to import data from AS400 Database to SQL Server 2008 database I receive an error

"Error Column "XXX" Cannot convert between unicode and non-unicode string data types"



Create data conversion specify column, add new column alias (i.e. new column name).  Be sure to specify enough length.




Afterwards remap to new Output alias in the destination

SSIS - Could not open global shared memory to communicate with performance DLL

When testing my SSIS package I received the following message:

Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.

My package takes data from one source to a database destination using SQL Server Destination.

I corrected this by using OLE DB Destination instead of SQL Server Destination as my target detination object.