December 20, 2006 at 7:12 am
I am quite new on SQL Server 2005. I am trying to import data into an Excel spreadsheet. I am using the OLE DB data item to connect to the database and am including a SQL command within the data item. Excel Destination data item is also used to import the data into the spreadsheet. I get the following error when trying to execute the SSIS package:
Error at Data Flow Task [Excel Destination [299]]: Column "RunDate" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [DTS.Pipeline]: "component "Excel Destination" (299)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
can anyone pls assist me on this?
December 20, 2006 at 9:24 am
Hi,
I think Excel text columns come through as Unicode and therefore you would need to put the into Nvarchar fields within SQL.
You can get around this by converting the text columns from you Excel source into DT_STR using a Data Conversion transform between your Source and Destination. You can then map the converted columns to your Varchar fields.
Hope this helps.
Regards
Daniel
December 21, 2006 at 12:16 am
Hi Daniel,
I tried what u suggested and it and the DTS runs fine, thanks. However none of the data is being displayed within the Excel spreadsheet. I am using the Data Conversion Data item in between the OLE DB and Excel Destination data items. When I run the SQL statement in Query Analyzer it works fine and brings back data.
Can u pls assist with this?
December 21, 2006 at 2:42 am
Hi,
I've just realised that I was back to front and you are exporting to excel rather than importing from. I have just created simple test package as a test this which worked ok for me however it was very basic.
On your datasource do you get any results if you click the preview button?
Do you get anything in the spreadsheet ie fieldnames?
Regards
Daniel
December 21, 2006 at 2:45 am
Hi Daniel,
I only get the columns names onto the spreadsheet. When I click preview in the datasource nothing is displayed, only the column names.
December 21, 2006 at 3:12 am
Well I guess the fact that you are not getting any results in the preview means you won't get any data in the Excel file.
The query works in query analyzer right? Is it a multiple statement query or an SP?
December 21, 2006 at 3:16 am
Hi,
The query works fine in Query Analyser. When clicking on the OLE Source Editor's preview button the data is displayed. It however does not want to display in the Excel Destination preview.
My query looks like this:
select
count(*) as Record_Count,
sum
(CAST(subscriber_balance AS decimal(20))) as Total_Value,
RunDate
from dbo.Data_Duplicates
where
Subscriber_Balance <> 'account_error'
and
rundate > (select convert(varchar(8),getdate()-7,112))
group
by RunDate
December 21, 2006 at 3:28 am
In your Data Flow check that the correct columns are being passed from the Source to the Data Conversion and to the Destination by double cliking on the connecting arrow and looking at the metadata tab.
What you can also try is adding a data viewer grid to each of the connectors to see what data is being passed between the transformations.
Regards
Daniel
December 21, 2006 at 3:52 am
Hi,
I don't know what happened but for some reason the DTS worked. Thanks for ur help.
Regards
Imke
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply