Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: DTS Package vs SSIS

    So i am creating SSIS packages from our current DTS Packages, not alot of them. I have come to a point where in the DTS package a column is coming from the source file (txt) like 20090321 this is a date. in the dts package that loads this data to a table which has the column as datetime there is no problem. this data is converted into date format i.e 2009-03-21 00:00:00.000 without any issues. i am positive there is no logic converting this data.
    Now this Stupid SSIS stuff is giving me an error about data loose. i am sure it is failing to convert 20090321 to a date format. MAY YOU PLEASE HELP ME WITH A SOLUTION. i tried using data convertion in SSIS, tried to change the column from the source to date, nothing is working. 911. thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you import the values as varchar?

    If yes run something like this query over your imported data
    Code:
    SELECT varchar_column
         , Convert(datetime, varchar_column)
    FROM   your_table
    If not can you provide the error messages you are receiving?

    In fact, are you getting any error messages as it stands?
    George
    Home | Blog

  3. #3
    Join Date
    May 2010
    Posts
    52
    Quote Originally Posted by gvee View Post
    Can you import the values as varchar?

    If yes run something like this query over your imported data
    Code:
    SELECT varchar_column
         , Convert(datetime, varchar_column)
    FROM   your_table
    If not can you provide the error messages you are receiving?

    In fact, are you getting any error messages as it stands?
    yes i am getting an error. something to do with data will be lost. i am home now can't give you the exact error message. so where do i type this code? because data is not getting to it's destination. i have 3 layers, source, data conversion and destination. the first 2 get the green light, it's only the destination that has a red. THANKS

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by mosdef View Post
    yes i am getting an error. something to do with data will be lost. i am home now can't give you the exact error message. so where do i type this code? because data is not getting to it's destination. i have 3 layers, source, data conversion and destination. the first 2 get the green light, it's only the destination that has a red. THANKS
    What is the datatype for destination? The lost data is truncation which happens for different datatype like source = varchar(100) and destination = char(25). Just an example.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change your destination to varchar, perform your import and then within a new query window inside SSMS, run the code I posted above (obviously edited to use your table and column names). Post back your findings.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by mosdef View Post
    MAY YOU PLEASE HELP ME WITH A SOLUTION
    Why SURE

    If you want to go back to the Paleozoic

    CREATE A TABLE that matches your load table

    EXCEPT make sure all your columns are varchar(max) and are nullable and call it stage_yourTablename

    EDIT: ooopps..the do a BULK INSERT to stage

    Then perform Data sanitation (check for dates that are date, numbers that are numbers, Foreign Keys, etc)

    If anything fails, create a report and HAVE THEM FIX THE DATA

    I would also suggest a separate Audit file that has the date and the number of expected rows

    Make sure the file is for the correct date, and the number of rows match the stage table

    If it's ok, and depending on whether you need to do a full treplace or not, there are several solutions..

    I like to do DELTA Processing

    Deltas

    MOO

    And good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2010
    Posts
    52
    thanks guys for your suggestions. well i decided i am going to tell them to give me a source file with a date format saves me time still don't know why a DTS Package was working Perfectly and a SSIS package cannot work on the same field. "BUG"

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I just don't trust them as tools...NO ONE has told me how to migrate the "code" through environments in a PVCS/ Source Safe scenario

    Besides...GUI's suck

    and tell me 1 thing you can do with those tools I can't do with SQL, bat files, sqlcmd and ftp??
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Thumbs up

    Quote Originally Posted by Brett Kaiser View Post
    GUI's suck

    and tell me 1 thing you can do with those tools I can't do with SQL, bat files, sqlcmd and ftp
    EXACTLY

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser View Post
    and tell me 1 thing you can do with those tools I can't do with SQL, bat files, sqlcmd and ftp??
    Globally change a setting on a whole class of objects (tables, views, stored procedures, etc) in a client's database within fifteen seconds. That was necessary yesterday to avert a serious problem for a new client.

    An accomplished DBA (especially a consultant) needs to be able to use both GUI and command line tools, and to know which is appropriate for a given task.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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