August 13, 2007 at 8:34 am
I'm trying to write data conversions in SSIS between a DB2 database on an IBM 6000 series with timestamp columns, and a SQL Server 2005 database with datetime columns. SSIS burps because it says that it might lose data in the conversion, and almost every one of the 228 tables I'm trying to convert have the *&%#%$ timestamp columns in them. I'm using the OLE DB sources for both ends of the conversion process, and have tried using the Data Conversion process to convert the timestamp into a DT-date format. Still get the error.
Any ideas?
Thanks!
Tim Brown
August 13, 2007 at 8:47 am
The DT_Date or The DT_time data types in SSIS Do not have a corresponding data type in SQL ( I believe they may have these Data types in SQL in the future.) If your destination is a SQL Datetime type then your source should be a Database TimeStamp.
August 13, 2007 at 9:02 am
Well, when I try selecting that value in the data conversion step as the output, I get the same error.
Right now I have three steps in the package: the OLE DB DB2 source step, the data conversion step that changes the timestamp value in DB2 to whatever (tried the timestamp value this last time) and the destination step which is OLE DB into SQL Server. Doesn't seem to want to work for whatever date value I select in the data conversion step.
August 13, 2007 at 9:10 am
on your oledb source for db2 if you right click select the advanced editor go to input output colums, What is the external data type for the colums and what is the out put data column type? If is is a string put a dataviewer on can you convert the string to datetime with a sql command?
August 13, 2007 at 9:25 am
Okay, in the advanced editor on the DB2 OLE DB source, under Column Mappings tab, the External Column Type is DT-DBTIMESTAMP, Database-timestamp. In Input and Output properties tab, says the same thing both for the external column and the output column format for the column.
What exactly do you mean by 'putting a dataviewer on'? That's a new one on me.
August 13, 2007 at 9:45 am
Looks like the source has read your first 100 lines and determined they all match the dt_DbtimeStamp so they should import dirctly to a sql table of datetime. Do yhe same for your SQL Source and make sure the destination for the mapped column is the same type. If you still getting errors then maybe it is just some rows and not all. If you right click the source then edit you can select error output. For the coulum on error you can select redirect row that will then allow you to connect the error (redline) to a destination so any rows that error wil be stored and not fail the whole step.
To put a dataviewer on right click the freen line select data viewers and add one (data grid). this will stop the transformation an allow you to view the data at that stage.(the data in the viewer is the data in memory if you hit the green button it loads the next set into memeory hit green until all rows processed or detatch to let it continue)
August 13, 2007 at 11:58 am
Mike, thanks, that got me past the timestamp schtick. Now I'm having a problem with a DB2 CLOB column in the same freakin' table. Any words of wisdom?
August 13, 2007 at 12:05 pm
The error messages are:
Error: 0xC020901C at Set 5 1, DB2 VENDOR_MASTER 1 [243]: There was an error with output column "COMMENTS" (329) on output "OLE DB Source Output" (254). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Set 5 1, DB2 VENDOR_MASTER 1 [243]: The "output column "COMMENTS" (329)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "COMMENTS" (329)" specifies failure on error. An error occurred on the specified object of the specified component.
Sounds like it can't make a connection to the DB2 database. I'm using the same OLE DB provider for all the other tables in DB2, and it's working fine.
August 14, 2007 at 3:04 am
Hi tim
Not sure what the problem with this error is. I think it is something to do with your DB" database not making that coulmn available for export. Not sure if it is an oledb driver problem for db2 and the nature and size of the colum.
August 14, 2007 at 3:31 am
A DB2 CLOB is a large object. It looks like you are trying to put this into a Comments field with a length of 329. This is likely to be the largest length fould in t eCLOB in the data that was sampled, but rows elsewhere in the table are longer.
The best SQL Server data type for a DB2 CLOB field is a SQL Text (or ntext) field, or maybe varchar(max) (or nvarchar(max)) in SQL 2005. If you try to put CLOB data into any other type of field you run the risk of data overflow.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 14, 2007 at 8:43 am
The output field in the SQL table is defined as a TEXT field. All the errors are coming from the DB2 side of the data flow for the table. It looks like the problem is in the DB2 OLE DB provider. That's why I'm trying to find out if there's something in the parameters that I need to switch or something to allow the CLOBs and BLOBs to be read by the MS DB2 OLE DB provider.
August 14, 2007 at 9:03 am
What ed said maybe right. Although your output data type is correct your input(external) datatype may not. ssis previews 100 records and determines what datatypes to set for the external data connection. It looks like it has set your clob datatype with a width of 329 but their is data which will not fit.
right click your oledb source (db2 connection) and select advaned editor select the input and output properties select oledb source input and the external columns, check the data type for the clob column and make sure it is correct for the clob type.
August 14, 2007 at 9:13 am
Looking at the error again i think it maybe a different problem. On the access mode under custom properties try changing to open rowset (dont use fast load) this will ignore the fast load options. do you get the error?
August 14, 2007 at 9:56 am
I'll give it a look as soon as I get the service pack for VS downloaded and run. I'm on a new computer this morning, and I'm still getting it set up. Thanks!
August 14, 2007 at 1:50 pm
Okay, here's the conditions:
DB2 table with a BLOB column ALTERNATE_CATEGORY_MASK
Task settings: I do have a specified directory for the BlobTempStoragePath
Data flow component Custom Properties for the DB2 link:
AccessMode: OpenRowset
AlwaysUseDefaultCodePage: True
DefaultCodePage: 1252
Input and Output properties for column ALTERNATE_CATEGORY_MASK:
External Column Datatype: Image(DT_IMAGE)
Output Column Datatype: Image(DT_IMAGE)
Error Listings:
Error: 0xC0202009 at Set 5 1 2, DB2 category_group [243]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC0208265 at Set 5 1 2, DB2 category_group [243]: Failed to retrieve long data for column "ALTERNATE_CATEGORY_MASK".
Error: 0xC020901C at Set 5 1 2, DB2 category_group [243]: There was an error with output column "ALTERNATE_CATEGORY_MASK" (7939) on output "OLE DB Source Output" (254). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Set 5 1 2, DB2 category_group [243]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ALTERNATE_CATEGORY_MASK" (7939)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "ALTERNATE_CATEGORY_MASK" (7939)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047038 at Set 5 1 2, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DB2 category_group" (243) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Error: 0xC0047039 at Set 5 1 2, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Error: 0xC0047021 at Set 5 1 2, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply