Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Unanswered: MS Access to SQL 2005

    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.

    Thanks,
    Jason.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can wither use a data conversion step or a derived column step in the SSIS package.

    Try setting the datatype to DT_STR (15)
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    The Edit Mappings section of the SSIS Import and Export Wizard allows you to specify what the destination field is.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2008
    Posts
    3
    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.

    error:
    [tblTest 1 [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.".

  6. #6
    Join Date
    Jul 2008
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •