October 23, 2012 at 9:26 am
Hello,
Is it just me or is the Excel Destination object pretty darn hard to get working? I am trying to write a package that runs a SQL stored procedure storing messages where data was ignored because it failed validation rules. The goal is to use an existing spreadsheet and fill it with error information.
The table used to store the error information is very simple:
create table [dbo].[Import_Failure] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[record_id] [int] NULL,
[failure_reason] [varchar](255) NULL)
The reasons in the table are all brief; none come close to the 255 character limit. For example, "missing SSN" is one.
In the package I have a conditional branch set up where the count of records on this table is greater than zero. If true, execution moves to a data flow task. This DFT has two components: An OleDB Source and the Excel Destination object.
The query for the OleDB Source is: select record_id, Failure_reason from Import_failure.
Now for the fun part! How in heck do I configure this this Excel object? What would seem to be simple thing to do is vexing.
I choose the OLE DB connection manager for it. I change the Data access mode to 'Table or view'. I click "New" where it says "Name of the Excel sheet." I get this message: "There is no sufficient information about mapping SSIS types to data types of the selected .NET data provider. As a result, you may need to modify the default column types of the SQL statement on the next screen."
I click OK. Then this SQL code appears in a dialogue box:
CREATE TABLE `Excel Destination` (
`record_id` INT,
`failure_reason` VARCHAR(255)
)
(Note: I have tried changing 'failure_reason' to NVARCHAR(255), to no avail.)
Click OK and move to the mappings area. The available input and available destination columns are the same, and already mapped. (record_id=>record_id; failure_reason=>failure_reason).
Click OK and return to the Data Flow pane where there is now a red error icon in the top right corner of the Excel Destination object. Hover the mouse over it and the tooltip says: "Column 'failure_reason' cannot convert between unicode and non-unicode string data types."
I debug the package and am told that failure_reason can't convert between unicode and non-unicode. Validation fails.
So configuring this Excel Destination object is turning into an ordeal. I can't believe it is this hard to get to work.
Can anyone help? Thanks!
October 23, 2012 at 10:06 am
Excel needs unicode data. Insert a data conversion task between your source query and Excel destination, converting all fields to unicode. That should work...
October 23, 2012 at 10:54 am
Martin, it worked like a charm. Thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply