1

I am trying to import Excel Data as a source to a SQL Server table destination. For the Excel source, when I try to click on the Source box and reset the lengths and types in the Advanced Editor, it keeps setting it the Excel Source import columns back to 255 length and DT_WSTR type. Some of my columns just need small unsigned integers and the 255 DT_WSTR is overkill. My searches on this subject find more about how to extend it beyond the 255 rather than to truncate from the start.

It seems that the Data Conversion Transformation doesn't exactly handle this issue maybe I am using it wrongly for this. Is there a way to truncate the data coming in without just shutting off the truncation error?

Improve this question

1 Answer 1

Reset to default
2

There are multiple solutions to that problem. The simplest one is to upload data AS IS to SQL staging table and then use T-SQL to convert to types you want.

You can use "Data Conversion" control, just do not forget to use "Aliased" names down the road. Also, you'd need to add error handling there.

enter image description here

Also you can use "Derived Column" control. There you "Add as new column", name it properly to use and specify conversion formula for your column. You can shrink your string right there and as you want. Also do not forget about error handling.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I went the Data Conversion route for this. Thank you for your time.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.