SSIS Excel Source wont let me change DT_DATE to DT_WSTR

Question

I have an SSIS package that imports data form an excel source to a SQL Server DB. The file has a column named [Client Date Of Birth] that is USUALLY a valid date. I say usually because the data is entered by our client's agents and can be anything from 02/24/2017 to Feb 17 or even just 2017. I actually need this data to come in as a string because we do partial comparison to these dates so data coming in as Feb 2017 is still technically okay.

The problem is SSIS automatically determines that this column is of [DT_DATE] type. I try to change the DataType to [DT_WSTR] in the External columns section of the Advanced Editor for the source, but when i click OK, SSIS automatically switches it back to [DT_DATE]. How do I get SSIS to import this column as a string?

It should also be noted that this is an automatic process so I can't make any changes to the excel file itself because it will be replaced by a new file each week.

Answer 1

There are many ways to achieve this:

First way

• Add a script component
• Mark [Client Date Of Birth] as an Input Column
• Create an Output Column [StrDOB] with Datatype equal [DT_STR]

• In the script write the following code (i assumed that you want the date format yyyy-MM-dd HH:mm:ss):

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  
      If Not Row.ClientDateOfBirth_IsNull Then
              Row.strDOB = Row.ClientDateOfBirth.ToString("yyyy-MM-dd HH:mm:ss")
          Else
              Row.strDOB_IsNull = True
      End If
  
  End Sub
  

Second Way

Use a Data Conversion Transformation component to achieve this. Just follow this MSDN links to get Help:

• https://msdn.microsoft.com/en-us/library/ms141706.aspx
• https://msdn.microsoft.com/en-us/library/ms140321.aspx
• https://msdn.microsoft.com/en-us/library/ms186792.aspx