February 26, 2010 at 5:16 am
Yes that's right. Problem is it doesn't load in to my sql table and the errorcode and errorcolumn output isn't very helpful in identifying which column is causing the problem. In my OLE DB Destination mapping I've set the population of my sql table, I've set it to ignore my derived column to see if that's fixed the redirection or error output and it still redirects which means I'm struggling to find out the reason...
February 26, 2010 at 5:45 am
In a previous post you said that the destination is a database timestamp field.
Do you mean that you use the datatype timestamp in SQL Server? Because that should be the datatype datetime instead. Timestamp is more of a "audit" data type, used to see if a row was updated or not.
(Don't get me started on the different data types between SSIS and SQL Server. Grmbl grmbl. That's what happens when different teams develop different products and those products have to work together...)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2010 at 6:04 am
da-zero (2/26/2010)
In a previous post you said that the destination is a database timestamp field.Do you mean that you use the datatype timestamp in SQL Server? Because that should be the datatype datetime instead. Timestamp is more of a "audit" data type, used to see if a row was updated or not.
(Don't get me started on the different data types between SSIS and SQL Server. Grmbl grmbl. That's what happens when different teams develop different products and those products have to work together...)
No, I'm using a datetime datatype in Sql, and in the derived column transformation my Data Type is database timestamp (DT_DBTIMESTAMP). Should I change the datatype in my derived column transformation to either database date (DT_DBDATE) or time (DT_DBTIME)?
February 26, 2010 at 6:30 am
Datetime is the correct datatype for SQL Server and (DT_DBTIMESTAMP) is the correct data type for SSIS. Since you're working with SQL Server 2005, don't convert the values to (DT_DBTIME), since the Time data type is a new datatype of SQL Server 2008.
However, there is an article on how to create your own date and time datatypes in SQL Server 2005:
http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx
But back to your problem. Have you tried adding a data viewer between the output of your derived column and the input of the OLE DB Destination? This way you can see if the output is in a wrong format.
(right-click on the green arrow, choose data viewers, click Add and finally choose Grid.)
If possible and if your data isn't too sensitive to post on the net, you can provide us with a screenshot of the data viewer.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2010 at 7:06 am
Yeah, but didn't notice anything that jumps out at me. Wish error trapping stuff like this in ssis was more intuitive!
I've attached a sample (3 rows) of the dataviewer as it redirects on every record, plus the structure of the table being populated.
February 26, 2010 at 7:39 am
I don't know what your mappings are in the OLE DB Destination, but if you map the data viewer column Time to the column Time in your destination table, it will give an error, as you are trying to put a bigint in a datetime column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2010 at 7:47 am
No, not doing that. Trying to put newtime in there i.e. the value that was created by the derived column. Doesn't work either. Not entirely convinced it's this that's causing the problem though. As, as you can see from this attachment, that I've even clicked on ignore to map to time and it still fails.
February 26, 2010 at 8:55 am
Worked it out through trial and error!!! Worked out that, although the data type on Sybase was Boolean I thought I could map it to bit on SQL but it looks like boolean is Y or N on sybase so had to change the data type on sql to nvarchar(1).
Must be an easier way to locate offending columns than trial and error excluding certain columns!
If you know of a better way of finding the erroneous column then let me know!
My times though, have gone in nicely. First record gone in as 1900-01-01 12:52:39.000 as expected.
Woo Hoo!
Thanks both again.
February 26, 2010 at 12:39 pm
Pfeuw, this thread has finally ended 🙂
When I delevop my packages, the OLE DB Destination usually gives an error or warning during design when I map a dataflow column to a database column with different datatypes.
Something in the like of:
cannot convert between datatype x and y
or
warning, truncation may occur bla bla bla
Maybe you can do the following check to see if the columns are set-up correctly (if you don't have too many columns):
place a data conversion transform right before the destination. Go into the editor and click on the first column name. Then, hold ctrl+shift and click on the last column name. All columns are selected. Then tick one checkbox. This places all the columns in the conversion pane below. There you can easily check for each column if it has the desired type.
Also, tape a paper to your wall that displays the mapping between SSIS-datatypes and SQL Server datatypes 🙂
(I sure did)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 26, 2010 at 1:13 pm
Griffster (2/26/2010)
Worked it out through trial and error!!! Worked out that, although the data type on Sybase was Boolean I thought I could map it to bit on SQL but it looks like boolean is Y or N on sybase so had to change the data type on sql to nvarchar(1).Must be an easier way to locate offending columns than trial and error excluding certain columns!
If you know of a better way of finding the erroneous column then let me know!
My times though, have gone in nicely. First record gone in as 1900-01-01 12:52:39.000 as expected.
Woo Hoo!
Thanks both again.
Did you check the Progress tab (becomes Execution Result when your package is finished) in BIDS? Normally you should find an informative error message there.
I have put On Error event handler in my packages that log the same errors to file and the database, based on this article.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply