SSIS Convert Between Unicode and Non-Unicode Error with LARGE Field

11 July 2016 Dave-Stuart SSIS, T-SQL

PROBLEM: I have been struggling with this error now for days now and have tried everything I know. I have an SQL statement that pulls data from several tables into another table. The field in question is a NTEXT field from a SQL 2000 database, which I now import into a SQL 2008 R2 table that is NVARCHAR(MAX) data type because I though the issue was the NTEXT data type. However the SSIS package that is just an OLE DB Source (with 1 field) into an Excel Destination is still giving me the Unicode and Non-Unicode Error!! Several rows of data are over 8000 characters in length.

ANSWER: After a lot of pain I finally came to the conclusion that Exporting to EXCEL is not possible so I turned to CSV. I used "Flat File Destination" object, pointed to a CSV that I had created with just the Headers. The Text Qualifier was set to double quotes. In the Columns section I set the Row delimiter to {CR}{LF} and the Column delimiter to Comma{,} because it is a CSV! The final part of the puzzle was to remove and double quotes, Carriage Returns and Line Feeds. I also had to convert the NTEXT field to VARCHAR(MAX) because REPLACE will not work with NTEXT. This is what I ended up with for the columns that had these "invalid characters".

REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX),[MyNTEXTColumn]), CHAR(13),' '), '"', ''), CHAR(10),'') AS 'Corrected Output'

I replaced {CR} CHAR(13) with a space to that we could have it formatted well for the consumer. I hope this helps someone out one day.

SSIS Excel connection manager error

30 May 2016 Dave-Stuart SSIS

PROBLEM: The package contains a simple "Execute SQL Task" that has a connectiontype "EXCEL" and connection set up to a pre configured Excel connection manager. 

The task is supposed to simply create a new spreadsheet using CREATE TABLE sql statement within the task.

It seems to run fine on a colleagues computer, however when im trying to run it on mine i experience the following error: "[Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager_LPL". Connection may not be configured correctly or you may not have the right permissions on this connection."

SOLUTION: I was running the package on my computer with the Runas64Bit project property as TRUE.  Changing this to false fixed the issue.  I believe this is because the version of drivers I have for excel - ssis connectivity are 32bit. In the Project Properties->Degugging Section,  I set Run64bit RunTime to False. It started working now.