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/

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. 

SQL Server 2008 Collation choices for Dynamics GP sort order 52

When installing Dynamics GP 2010 on a SQL Server the end collation will be based on the Regional Settings that your server is running under.

English (United Kingdom) - Latin1_General_CI_AS


English (United States) - SQL_Latin1_General_CP1_CI_AS
Both are supported to use as Sort Order 52 for Microsoft Dynamics GP 2010.  However you can run a SQL_Latin1_General_CP1_CI_AS company database on a server running Latin1_General_CI_AS


The following website link shows the language setting and the collation defined by default
http://technet.microsoft.com/en-us/library/ms143508(SQL.90).aspx

The Dynamics GP Blogster: Granting Microsoft Dynamics GP user minimal access...

The Dynamics GP Blogster: Granting Microsoft Dynamics GP user minimal access...: "After the long title of this post, you probably already have the idea of what the article will be about. However, back in April of 2009, I w..."

How to transfer logins and passwords between instances of SQL Server

Fantastic article from Microsoft to transfer logins between SQL Server installs.  Pefect for server moves of SQL 7, 2000, 2005 and 2008

http://support.microsoft.com/kb/246133

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance - script

The following document was from the great Pinal Dave on from the following website: http://www.sqlauthority.com/. This article was extremely useful when I was troubleshooting performance issues in Dynamics GP:


Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://www.sqlauthority.com/)

Microsoft SQL Server 2008 R2 November CTP Report Builder 3.0

Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 R2 Reporting Services. The download provides a stand-alone installer for Report Builder 3.0.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=f78b6b1e-8ccb-407a-bc3e-7955d60e1a6c