Search code examples
sql-serverssis-2012

SSIS 2013 cannot convert between unicode and non-unicode string data types

I have done quite a bit of googling on this one and I'm stumped. I'm trying to set up an SSIS job that will truncate a table and then import data from an excel source. I have the truncate working, I've created multiple jobs that have done this before, just never seen this error. During all my googling, they pretty much have all said to go into the Advanced Editor and change the input and output properties. I seemed to have no luck with that, still got the same error. Here are the images of the default settings.

Error:

enter image description here

Destination:

enter image description here

Excel:

enter image description here

Solution

  • Check the data type in the destination table. I believe the Unicode output will work with NVarchar. If the data type at the destination is Varchar then use string.

    enter image description here

    In my experience loading excel files always causes grief. I prefer to convert to .txt first and have found that process to be much more reliable. There are 3rd party offerings that provide Powershell components or you can use the Execute Process Task to kick off the script.

    $FolderLoc = "<filepath>"
$files = Get-ChildItem $FolderLoc\*.xlsx

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $FALSE
$Excel.DisplayAlerts = $FALSE

foreach ($file in $files) {
     Write "Loading File '$($file.Name)'..."
     $WorkBook = $Excel.Workbooks.Open($file.Fullname)
     $NewFilePath = [System.IO.Path]::ChangeExtension($file.Fullname,".txt")
     $Workbook.SaveAs($NewFilepath, 42)   # xlUnicodeText
}

# cleanup
$Excel.Quit()