Problem with simple SSIS package

I am trying to copy rows from a SQL query into an existing excel file.
The errors I am getting are:

[Excel Destination [1381]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [1381]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

The t-sql is made up of several columns - most are actual columns and some are aliased empty strings so that there is a like for like match to the spreadsheet.
When I look at column mapping everything looks perfect but I just can't get it to work.
I must be missing something simple. I look at the meta data for the data flow and wonder if there is a mismatch in datatypes (shown in the image) but I can't see a way to change the datatypes

metadata.png
LVL 29
QPRAsked:
Who is Participating?
 
edtechdbaCommented:
If you use a data conversion task in-between the data source and excel destination, you should be able to get the data types to match, map the converted column values to the destination columns.

I like to use file system tasks to copy my "template" file (a blank excel file with the proper headers) into my source file location, then run the data import process in the data flow task. You can set your template file to overwrite the data source file so that it's a prep step prior to every run. That way you know you have a fully refreshed file every run.
0
 
edtechdbaCommented:
You may want to check the mapping properties on your excel destination in the data flow task, hover over the input fields and output fields and make sure the data types match.

I find that often times I need to use a data conversion task in order to change the data type to match.
0
 
QPRAuthor Commented:
They do not match:
int > string
ntext > string

Is this what would cause those cryptic errors?

I think you may be right with the transformation in task, in fact the more I look at what I need to achieve the more I see that it might not be a simple data pump task.
For instance I need to recreate the spreadsheet each time so may use a template with the headers already in place.
0
 
QPRAuthor Commented:
Great thanks - no doubt ill be back with more questions
0
 
edtechdbaCommented:
Good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.