SQL Server 2008 Client Import Export Wizard

Question

SQL Server 2008 Client Import Export Wizard

nazareena.sulthana

SSC-Addicted

Points: 476
More actions
January 4, 2012 at 11:25 pm

#150760

SQL Server 2008 Client Import Export Wizard
for timestamp field iam getting error in the conversion in the source data type it shows 135. please help me on this

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Charmer SSChampion Points: 14903 More actions · Answer 1

nazareena.sulthana (1/4/2012)
SQL Server 2008 Client Import Export Wizard
for timestamp field iam getting error in the conversion in the source data type it shows 135. please help me on this

this is not enough details you give us to help you....provide more so that you can help us to help you...

Thanks,
Charmer

nazareena.sulthana SSC-Addicted Points: 476 More actions · Answer 2

Hi

Thanks for the response i have solved that issue by modifying the related Mapping file (data type DT_DBTIMESTAMP for that 135 datatype mapping).

But after that also when i try to import the table having timestamp field iam getting the following error

Messages

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.".

(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with input column "CURRENT_TIMESTAMP" (214) on input "Destination Input" (205). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (205)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (205)" 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.

(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 3 - LOG_TABLE" (192) failed with error code 0xC0209029 while processing input "Destination Input" (205). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Charmer SSChampion Points: 14903 More actions · Answer 3

nazareena.sulthana (1/5/2012)
Hi
Thanks for the response i have solved that issue by modifying the related Mapping file (data type DT_DBTIMESTAMP for that 135 datatype mapping).
But after that also when i try to import the table having timestamp field iam getting the following error
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with input column "CURRENT_TIMESTAMP" (214) on input "Destination Input" (205). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (205)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (205)" 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.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 3 - LOG_TABLE" (192) failed with error code 0xC0209029 while processing input "Destination Input" (205). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

i am not getting detailed info by you to help you.....any how, check what is the destination data type? ....give the correct data type that matching the destination...if that was correct data type matching, then try with this...type... DT_DBTIMESTAMP2 ....

still getting error..you can come up with the issue...we will try to solve that...Good Luck

Thanks,
Charmer

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Please note that the native TIMESTAMP is not a DATETIME type.

Charmer SSChampion Points: 14903 More actions · Answer 5

Revenant (1/6/2012)
Please note that the native TIMESTAMP is not a DATETIME type.

i agree with you..i hope she is not using Timestamp for datetime

Thanks,
Charmer

nazareena.sulthana SSC-Addicted Points: 476 More actions · Answer 6

Actually Iam using Import export wizard to migrate the data from DB2 to SQL Server 2008.

As i said before i used in my mapping for timestamp (135) to DT_DBTIMESTAMP as follows

<dtm:DataTypeMapping >

<dtm:SourceDataType>

<dtm:DataTypeName>135</dtm:DataTypeName>

</dtm:SourceDataType>

<dtm:DestinationDataType>

<dtm:NumericType>

<dtm:DataTypeName>DT_DBTIMESTAMP</dtm:DataTypeName>

<dtm:SkipPrecision/>

<dtm:UseSourceScale/>

</dtm:NumericType>

</dtm:DestinationDataType>

</dtm:DataTypeMapping>

Then iam getting the above overflow error as mentioned in my previous post.

After your reply i changed to DT_DBTIMESTAMP2

<dtm:DataTypeMapping >

<dtm:SourceDataType>

<dtm:DataTypeName>135</dtm:DataTypeName>

</dtm:SourceDataType>

<dtm:DestinationDataType>

<dtm:NumericType>

<dtm:DataTypeName>DT_DBTIMESTAMP2</dtm:DataTypeName>

<dtm:SkipPrecision/>

<dtm:UseSourceScale/>

</dtm:NumericType>

</dtm:DestinationDataType>

</dtm:DataTypeMapping>

when i used above again same error

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.".

(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with input column "PROC_DATE" (73) on input "Destination Input" (53). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (53)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (53)" 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.

(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - FIXGSS_SELECT3" (40) failed with error code 0xC0209029 while processing input "Destination Input" (53). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - FIXGSS_SELECT3" (1) returned error code 0xC02020C4. 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.

(SQL Server Import and Export Wizard)

please help me on this

Thanks in Advance

Nazareena

Charmer SSChampion Points: 14903 More actions · Answer 7

nazareena.sulthana (1/6/2012)
Actually Iam using Import export wizard to migrate the data from DB2 to SQL Server 2008.
As i said before i used in my mapping for timestamp (135) to DT_DBTIMESTAMP as follows
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>135</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:NumericType>
<dtm:DataTypeName>DT_DBTIMESTAMP</dtm:DataTypeName>
<dtm:SkipPrecision/>
<dtm:UseSourceScale/>
</dtm:NumericType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
Then iam getting the above overflow error as mentioned in my previous post.
After your reply i changed to DT_DBTIMESTAMP2
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>135</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:NumericType>
<dtm:DataTypeName>DT_DBTIMESTAMP2</dtm:DataTypeName>
<dtm:SkipPrecision/>
<dtm:UseSourceScale/>
</dtm:NumericType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
when i used above again same error
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error.".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with input column "PROC_DATE" (73) on input "Destination Input" (53). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (53)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (53)" 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.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - FIXGSS_SELECT3" (40) failed with error code 0xC0209029 while processing input "Destination Input" (53). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - FIXGSS_SELECT3" (1) returned error code 0xC02020C4. 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.
(SQL Server Import and Export Wizard)
please help me on this
Thanks in Advance
Nazareena

i hope this is due to SQL server Compatibility problem.....

Are you using Microsoft SQL Server Native Client 10.0?

take a look at this URL..you may get some ideas..

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

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 8

can you post some of your sample date values in here..?

if your value is like this YYYY-MM-DD-HH.MM.SS.000000.

it will not be supported by the SQL...

so try to change the format

Thanks,
Charmer

nazareena.sulthana SSC-Addicted Points: 476 More actions · Answer 9

Thanks for the response

yes iam using Microsoft SQL Server Native Client 10.0

Actually SQL database is in MS SQL Server 2005 and import wizard

iam using MSSQL Server 2008 client is this will create any problem

the timestamp value is formate is 2008-11-01 06:52:00.513001

so i think i have to use DT_DBTIMESTAMP2

datetime2

but in desination source type drop down (in edit mapping) i can see only datetime

so how to go about this.

could you pease let me know howto change the format.

i mean before import i should change all the date value format and then try importing?

Thanks in Advance

Nazareena

Charmer SSChampion Points: 14903 More actions · Answer 10

nazareena.sulthana (1/6/2012)
Thanks for the response
yes iam using Microsoft SQL Server Native Client 10.0
Actually SQL database is in MS SQL Server 2005 and import wizard
iam using MSSQL Server 2008 client is this will create any problem
the timestamp value is formate is 01 06:52:00.513001
so i think i have to use DT_DBTIMESTAMP2
datetime2
but in desination source type drop down (in edit mapping) i can see only datetime
so how to go about this.
Thanks in Advance
Nazareena

remove those dots and try to modify using this condition..it may help you

SUBSTRING([TimeColumn],1,10) + " " + REPLACE(SUBSTRING([TimeColumn],12,8),".",":") + RIGHT([TimeColumn],7)

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 11

use data conversion transformation to check with all the date type formats...

if it ain't solve the problem try to use derived column transformation with some replace and sub strings...like i said

if the above does not work...then it is server provider problem..try to use different SQL provider in the connection string (old provider)

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 12

nazareena.sulthana (1/6/2012)
Thanks for the response
yes iam using Microsoft SQL Server Native Client 10.0
Actually SQL database is in MS SQL Server 2005 and import wizard
iam using MSSQL Server 2008 client is this will create any problem
the timestamp value is formate is 2008-11-01 06:52:00.513001
so i think i have to use DT_DBTIMESTAMP2
datetime2
but in desination source type drop down (in edit mapping) i can see only datetime
so how to go about this.
could you pease let me know howto change the format.
i mean before import i should change all the date value format and then try importing?
Thanks in Advance
Nazareena

if DB2 data type is timestamp then data type must be changed to datetime2

Thanks,
Charmer

nazareena.sulthana SSC-Addicted Points: 476 More actions · Answer 13

Iam getting the same overflow error.

By chaning the datasource to Microdoft OLE DB provider for SQL Server inserted properly the values in one table which was failiing. But again the following error for other few tables having data field table.

date format is 1999-06-08 00:00:00.0

Messages

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".

(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with input column "CRTD_DT" (60) on input "Destination Input" (47). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (47)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (47)" 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.

(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SSMREV_H" (34) failed with error code 0xC0209029 while processing input "Destination Input" (47). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - SSMREV_H" (1) returned error code 0xC02020C4. 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.

(SQL Server Import and Export Wizard)

Please suggest to solve this.

One more question can we import the primary key , forgein key constraints also...

Similarly can we import sequence also using the import export wizard?

Thanks

Nazareena

Charmer SSChampion Points: 14903 More actions · Answer 14

nazareena.sulthana (1/9/2012)
Iam getting the same overflow error.
By chaning the datasource to Microdoft OLE DB provider for SQL Server inserted properly the values in one table which was failiing. But again the following error for other few tables having data field table.
date format is 1999-06-08 00:00:00.0
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task 1: There was an error with input column "CRTD_DT" (60) on input "Destination Input" (47). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (47)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (47)" 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.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SSMREV_H" (34) failed with error code 0xC0209029 while processing input "Destination Input" (47). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - SSMREV_H" (1) returned error code 0xC02020C4. 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.
(SQL Server Import and Export Wizard)
Please suggest to solve this.
One more question can we import the primary key , forgein key constraints also...
Similarly can we import sequence also using the import export wizard?
Thanks
Nazareena

for the date problem, if any of those solutions have not solved, it may be SQL server provider problem then. try to use with old version. This would be the last answer from me for this question...

yes, we can import keys...

Thanks,
Charmer