When an excel data source is used in SSIS, the data types of each individual column are derived from the data in the columns. How do we override this behaviour?
Ideally we would like every column delivered from the excel source to be string data type, so that data validation can be performed on the data received from the source in a later step in the data flow.
Just go into the output column list on the Excel source and set the type for each of the columns. Let excel do its guessing game. We are interested in output column and type. This works.
To get to the input columns list right click on the Excel source, select ‘Show Advanced Editor’, click the tab labeled ‘Input and Output Properties’.
A potentially better solution is to use the derived column component where you can actually build “new” columns for each column in Excel. This has the benefits of
- You have more control over what you convert to.
- You can put in rules that control the change (i.e. if null give me an empty string, but if there is data then give me the data as a string)
- Your data source is not tied directly to the rest of the process (i.e. you can change the source and the only place you will need to do work is in the derived column)
This is another work around but it does not work at run time. You can see the data at design time though;
Write your SQL command and convert columns to text.
You can verify this on Advance tab / Input and Output Properties tab of Excel source. All of converted columns under External Column would be changed to “Unicode text stream” data type
Add to favorites
