none
Data Conversions SSIS to SQL 2005

    Question

  • Is there a Data Conversion cross reference list from SSIS data types to SQL 2005 types.

     

    The one I am looking for in particular:

     

    SQL 2005                               SSIS

    smalldatetime                           ?? 

     

     

    I keep getting data conversion errors trying to write GetDate() from SSIS to SQL 2005 when using the SSIS date types.

     

    One would have thought Microsoft would have included the same datatypes in SSIS as the fieldtypes in SQL 2005, since the primary task of SSIS packages is to write and transform data to SQL Server 2005 tables.

     

    Can someone shed some light?

     

    Thanks!

     

    SkySeek

    Thursday, August 14, 2008 1:37 PM

All replies

  •  

     

    May be this can help:

    http://msdn.microsoft.com/en-us/library/ms141036.aspx

     

     

     

    Thursday, August 14, 2008 5:12 PM
    Moderator
  • Thanks but this didn't help.  Just to add more clarification, we are looking up a derived column of DT_DATE  SSIS format against a smalldatetime filed in the database and SSIS is baulking at doing this.

     

    Any ideas how to "specifically" define a "smalldatetime" derived column in SSIS, pass in todays date (don't care about time, would like it 00:00:00) and have it be used to lookup a smalldatetime field in a SQL 2005 table.

     

    Again, SSIS is baulking at doing this lookup because the datatypes are different?

     

    Thanks again!

     

    SKYSEEK

    Friday, August 15, 2008 2:18 PM
  •  

    I have tried all of these date types, and SSIS is balking at looking up a smalldatetime field in SQL 2005.    Do you have to cast them or something?  It keeps saying the at datatypes don't match" for this type of lookup.  i have tried deriving from a variable, but still I get the same error.

     

     

    Thanks for any help!

     

    Thanks!

     

    SkySeek

     

     

    Friday, August 15, 2008 2:45 PM
  • Then short answer is "no".  There is no direct translation of data types and Yes, I wondered about what MS was thinking when they did not get the data types to match.

     

    I needed to resort to some goofy manipulations in SSIS to get the dates data type on the same format.  The code I used is listed below:

     

    CONVERT(CHAR(10), SalesDate.SalesDate, 112) =" + "'" + REPLACE((DT_WSTR,30) (DT_DBDATE) @[User:Stick out tonguereviousDay] ,"-","")  + "'" 

     

    The first part converts the date from SQL Server to yyyymmdd format as I did not want the time either.

     

    Now, for the right hand the variable PreviousDay has the following formula:

     

    (DT_DBTIMESTAMP) (DT_DBDATE)  DATEADD( "day", -1, GETDATE()  ) 

     

    The formula above converts the date to 12:00:00 of the previous day. 

     

    Now, back to the formula in RED, the DT_DBDATE function here strips the time away and returns the format - 2008-08-14; you need to use the (DT_WSTR,30) to convert to a string or else you get an error.  Now, the REPLACE removes the - and gives you the 20080814 format.

     

    That is it! 

     

    I am not sure what you are specifically trying to accomplish but I was trying to read data from a SQL Server table for a particular previous day and so used the formula in RED in my SQL query.

     

    Friday, August 15, 2008 5:03 PM
  •  

    Thanks for the code snippets.  I am a little confused.  I found the Replace function but not the CONVERT.  Is that part of TSQL?  It Errored on CONVERT.

     

    Please clarify.

     

    Thanks!

     

    Monday, August 18, 2008 7:20 PM


  • Convert is a T-SQL function. Perhaps you are getting a syntax error in the SSIS expression.

    Please provide the SSIS expression and any of the folks around would validated for you.
    Tuesday, August 19, 2008 1:56 AM
    Moderator
  • I apologize for not making my use of the expression clear.

     

    I am using the code within a variable wherein I am passing the entire SQL statement as a text string within a variable.

     

     

    Thursday, August 21, 2008 7:55 PM
  •  

    Hi SkySeek,

     

    If the table you are looking up against has a column of type smalldatetime called "SmallDateT", in SSIS this column type will be treated as DT_DBTIMESTAMP. If you compare this column with another that is of type DT_DATE called "MyDate", lookup transform will not allow that.

     

    So you need to add a derived column before your lookup transform called "Test" and set the expression as (DT_DBTIMESTAMP)MyDate. In your Lookup transform Columns tab, if you now try to connect column "Test" and "SmallDateT", it should work fine.

     

    Regarding your other question about how to add a derived column of type "smalldatetime" in SSIS and pass in today's date. It is similar to what I suggested earlier, you'd need to add a derived column say "Test2" and set the expression as GETDATE() and you will notice that the data type for "Test2" column is already set to "DT_DBTIMESTAMP". In your Lookup transform Columns tab, if you now try to connect column "Test2" and "SmallDateT", it should work fine since they are both of type DT_DBTIMESTAMP.

     

    Thanks,

    Ritu

    Wednesday, August 27, 2008 10:31 PM