SSIS Package Exporting to Access

• cmoliver

  Old Hand

  Points: 345

  More actions

  January 10, 2008 at 8:14 am

  #120196

  I'm just starting with SSIS and have managed to successfully migrate all of my DTS packages except for one. Yay.

  This package basically takes a view from a SQL 2005 database and exports the view into an Access database every four hours so that another piece of software can reference it. I can't get this export to work because of the "can't convert between unicode and non-unicode string data types" error. I am using a Data Conversion. The source data is DT_STRING and I'm converting to DT_WSTRING, because that seems to be what Access wants to take. I can't change the data type in the OLE DB Destination (the Access database), because it always changes back to DT_WSTRING, which I believe is an Access quirk.

  Any ideas on what I need to do to get around this? It's pretty urgent, since my last day at the company is tomorrow and no one else here knows SQL at all. 🙂

  -Charlotte

• Dave23

  Hall of Fame

  Points: 3873

  More actions

  January 10, 2008 at 8:48 am

  #766699

  If all else fails, you can apply a type cast in a Derived Column component in your data flow to change the inbound DT_STR to an outbound DT_WSTR:

  Derived Column Name: UnicodeOutVal

  Derived Column:

  Expression: (DT_WSTR, )[SomeStringField]

  DataType: Unicode string [DT_WSTR]