I have found lots of help on this subject, but none of the threads spell out exactly what I need to do. I have an Access database that I need to convert to SQL2005. I'm using SSIS. Some of the columns in Access are of the datatype 'text'. I need them to be Varchar(15) in the destination SQL table. I have several Access databases to convert. I can not modify the Access database. The process needs to be automated and not a manual task.
I have tried using a Data Conversion in SSIS. I keep getting the "cannot convert between unicode and non-unicode." errors in SSIS.
How "Exactly" do I use the Data Conversion in SSIS? Is there an easier way to do this?
The way we move things from Test to Prod, the old BCP utility would not be an option.
I had found I needed a Data Conversion in my Data Flow, I just couldn't figure out how to set it up. A co-worker showed me that my Output Alias's were wrong. I changed them to 'copy of...' for each column and that worked.
Now I've moved on to a new issue.
Is there an easy way to ignore truncation? There is a comment field that I want to limit to a varchar(1000). In access it was text.
[tblTest 1 ] Error: There was an error with output column "SentComments" (30) on output "OLE DB Source Output" (12). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Sorry, correction. The Access db has the column as Memo. I ran a max len and the longest data is only 251. The destination SQL table is Varchar(1000).
On the data Conversion I checked the Advanced Editor. Input and Output Properties, found the column from the error and selected "TruncationRowDisposition" and chose "RD_IgnoreFailure".
I then tried "ErrorRowDisposition" and chose "RD_IgnoreFailure" for the column in the error.