August 14, 2011 at 3:43 pm
Added an column (added date) through Derived Transformation... made sure it is timestamp Datetype. My destination table has a Data type as Smalltime. It shows something with the datatype conversion error ...If at all their needs to be change in the data type of added date what should it be ???? help me
August 14, 2011 at 4:07 pm
If that's the only change you've made to the package and that's when the error occured then yes it's a very good bet that you have dates outside the smalldatetime datatype.
What have you tried to detect those?
August 14, 2011 at 5:56 pm
What should I be doing for these... This is strange for me, as I colud do this package sucessfully without any kind of errors during Dev.
August 14, 2011 at 6:23 pm
according with microsoft's article (link bellow), is necessary to do explicit convertion, for example: cast(test as smalldatetime).
http://msdn.microsoft.com/en-us/library/ms187928.aspx
in SSIS you can to use the Data Conversion Transformation (component).
August 14, 2011 at 10:02 pm
I tried to cast the above expression, but it's gives me an casting error (red color) in the Data conversion Transformation....
August 15, 2011 at 5:09 am
post the error that show in progress tab!
August 15, 2011 at 9:12 am
There was an error with input column "Added Date" (86) on input "OLE DB Destination Input" (47). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
August 15, 2011 at 9:16 am
quillis131 (8/15/2011)
There was an error with input column "Added Date" (86) on input "OLE DB Destination Input" (47). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
It was running very sucessful, during Development.Verfied the Destination table Datatype on the Dev box ..its SMALLDATE and the equilavent Datatype should be Dt_DBTImeSTAMP right ???
August 15, 2011 at 9:54 am
according with microsoft's article http://msdn.microsoft.com/en-us/library/ms141704.aspx that show the convesions illegal/legal to differents data types of SSIS.
quillis131 (8/15/2011) It was running very sucessful, during Development.Verfied the Destination table Datatype on the Dev box ..its SMALLDATE and the equilavent Datatype should be Dt_DBTImeSTAMP right ???
smalldatetime isn't equivalent dt_dbtimestamp, because a timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a fixed scale of 3 digits.
smalldatetime not contains in your struture seconds and fractional seconds!!!!
August 15, 2011 at 9:55 am
your conversion is dt_dbtimestamp to smalldatetime???
August 15, 2011 at 10:35 am
rfr.ferrari (8/15/2011)
according with microsoft's article http://msdn.microsoft.com/en-us/library/ms141704.aspx that show the convesions illegal/legal to differents data types of SSIS.quillis131 (8/15/2011) It was running very sucessful, during Development.Verfied the Destination table Datatype on the Dev box ..its SMALLDATE and the equilavent Datatype should be Dt_DBTImeSTAMP right ???
smalldatetime isn't equivalent dt_dbtimestamp, because a timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The fractional seconds have a fixed scale of 3 digits.
smalldatetime not contains in your struture seconds and fractional seconds!!!!
Then What should be the Equivalent Datatype for SMALLDATETIME(sql) datatype in SSIS
August 15, 2011 at 10:41 am
rfr.ferrari (8/15/2011)
your conversion is dt_dbtimestamp to smalldatetime???
Have to do something like this.... I have a column (which holds Date) with Datatype Smalldatetime in the Destination table. Have to somehow send a column ( from Data conversion Transformation) with equilvalent datatype that matches to Smalldatetime datatype in the Destination.
August 15, 2011 at 11:17 am
DBTIMESTAMP is the correct variable to pass in to a SMALLDATETIME field. See this page for more info:
http://msdn.microsoft.com/en-us/library/ms141036%28v=SQL.90%29.aspx
Check all your meta data types. Specifically, look at the meta data on the path going into your OLE DB Destination. Make sure that your date column going in to the OLE DB Destination has the correct data type of DBTIMESTAMP. Make sure that your date column in the server is SMALLDATETIME.
If all of those check out, then trace your meta data backwards at each step, and find the task which is setting the meta data of the column to DBTIMESTAMP. If, before this step, the data type was something different, then that would be where you would want to watch for data conversions. You can use the data viewers to see all the data passing through and observe which is causing your script to crash.
August 15, 2011 at 11:35 am
kramaswamy (8/15/2011)
DBTIMESTAMP is the correct variable to pass in to a SMALLDATETIME field. See this page for more info:http://msdn.microsoft.com/en-us/library/ms141036%28v=SQL.90%29.aspx
Check all your meta data types. Specifically, look at the meta data on the path going into your OLE DB Destination. Make sure that your date column going in to the OLE DB Destination has the correct data type of DBTIMESTAMP. Make sure that your date column in the server is SMALLDATETIME.
If all of those check out, then trace your meta data backwards at each step, and find the task which is setting the meta data of the column to DBTIMESTAMP. If, before this step, the data type was something different, then that would be where you would want to watch for data conversions. You can use the data viewers to see all the data passing through and observe which is causing your script to crash.
I did the same way ... Intially extracting Some Data from the Orcale and then pushing them into Sql, along with that making sure that a Date column is updated in the Destnation Table, My Derived column does this stuff.
What makes me Nuts: It was all good in Dev environment, and then deployed it onto Producation ( here it turns out to be crashed) with an errror saying that overflowed...Its very suprising to me.....
August 15, 2011 at 11:56 am
what's your data source??
is a table? in another server, for example Oracle or Text File?
the destination table, we know!!
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply