SSIS import and export wizard 搞不定了 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SSIS import and export wizard 搞不定了

SSIS import and export wizard 搞不定了# Database - 数据库

B*L2009-04-06 07:04

1 楼

我用SSIS import and export wizard 从Oracle 数据库import 50个表到sql2005. 这
个Package 有时候success,有时候fail, fail的时候不定在那个TABLE，但是error
message 都一样的。
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native
Client" Hresult: 0x80004005 Description: "Invalid character value for cast
specification".
我试着把package 变成一个table一个Data Flow Task然后串起来RUN，但是有这个
ERROR。
大家看看有什么办法呢？
谢谢了。

j*n2009-04-06 07:04

2 楼

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/98f22137-caac-42e4-a229-c9629c957262/

code:
cast

【在 B*********L 的大作中提到】

: 我用SSIS import and export wizard 从Oracle 数据库import 50个表到sql2005. 这
: 个Package 有时候success,有时候fail, fail的时候不定在那个TABLE，但是error
: message 都一样的。
: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code:
: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native
: Client" Hresult: 0x80004005 Description: "Invalid character value for cast
: specification".
: 我试着把package 变成一个table一个Data Flow Task然后串起来RUN，但是有这个
: ERROR。
: 大家看看有什么办法呢？

B*L2009-04-06 07:04

3 楼

多谢前辈！
我照着下面这个回复改了。It works!
Maurice Maglalang Wednesday, January 23, 2008 7:25:22 AM
change ERROR OUTPUT within your dataflow task to IGNORE FAILURE on the
offending field within your source. do the same to your destination then
you'll be good to go bro...ignore those worthless answers above...l8

j*n2009-04-06 07:04

4 楼

usually I'd like do more research to see what's the error value is. just in
case if missing some information.
you can try redirect to another text file and check.

【在 B*********L 的大作中提到】

: 多谢前辈！
: 我照着下面这个回复改了。It works!
: Maurice Maglalang Wednesday, January 23, 2008 7:25:22 AM
: change ERROR OUTPUT within your dataflow task to IGNORE FAILURE on the
: offending field within your source. do the same to your destination then
: you'll be good to go bro...ignore those worthless answers above...l8

B*g2009-04-06 07:04

5 楼

not available for express?

in

【在 j*****n 的大作中提到】

: usually I'd like do more research to see what's the error value is. just in
: case if missing some information.
: you can try redirect to another text file and check.

j*n2009-04-06 07:04

6 楼

don't think so... try to download full functional evaluate version for 2k8.
2k5 has 180 days, good enough.

【在 B*****g 的大作中提到】

: not available for express?
:
: in

B*g2009-04-06 07:04

7 楼

I need free and unlimited.

.

【在 j*****n 的大作中提到】

: don't think so... try to download full functional evaluate version for 2k8.
: 2k5 has 180 days, good enough.

B*L2009-04-06 07:04

8 楼

developer edition has all functions and very cheap.

【在 B*****g 的大作中提到】

: I need free and unlimited.
:
: .

B*L2009-04-06 07:04

9 楼

in
I tried to redirect error rows to a txt file. In each run, I got complete
different rows. However, the error code and error description are same:
-1071607686,Conversion failed because the data value overflowed the type
used by the provider.

【在 j*****n 的大作中提到】

: usually I'd like do more research to see what's the error value is. just in
: case if missing some information.
: you can try redirect to another text file and check.

B*g2009-04-06 07:04

10 楼

1. no matter how cheap, it is not free.
2. can not use for production or even test.

【在 B*********L 的大作中提到】

:
: in
: I tried to redirect error rows to a txt file. In each run, I got complete
: different rows. However, the error code and error description are same:
: -1071607686,Conversion failed because the data value overflowed the type
: used by the provider.

B*L2009-04-06 07:04

11 楼

才50刀。Express上，浪费的小时值多少？
另外test肯定可以。

【在 B*****g 的大作中提到】

: 1. no matter how cheap, it is not free.
: 2. can not use for production or even test.

B*g2009-04-06 07:04

12 楼

只要是花钱，公司就不会买。

【在 B*********L 的大作中提到】

:
: 才50刀。Express上，浪费的小时值多少？
: 另外test肯定可以。

w*e2009-04-06 07:04

13 楼

kao, 太cheap了点吧

【在 B*****g 的大作中提到】

: 只要是花钱，公司就不会买。

B*L2009-04-06 07:04

14 楼

大概也许可能找到原因了，但是不知道怎么处理。
Oracle：Character set (AL32UTF8)
SQL Server: Client character set (WE8MSWIN1252)
They are different.
现在的 SSIS import 总是随机出错。我的package里用的是defaultcodepage=1252，不
知道怎样才能选AL32UTF8。
麻烦大家指点一下，谢谢。

j*n2009-04-06 07:04

15 楼

Here is a dummy solution....
use SSIS to export Oracle data into a text file (Delimited);
use SSIS to import this text file into your destination table. Better
predefine your destination table first, otherwise the data type may differ
when you create it while you import.
Another solution is that use data conversion component, which could do some
type transformations but is limited.
Sure, changing collations is always a pain in the S.
Let me know which one worked well for you.

【在 B*********L 的大作中提到】

: 大概也许可能找到原因了，但是不知道怎么处理。
: Oracle：Character set (AL32UTF8)
: SQL Server: Client character set (WE8MSWIN1252)
: They are different.
: 现在的 SSIS import 总是随机出错。我的package里用的是defaultcodepage=1252，不
: 知道怎样才能选AL32UTF8。
: 麻烦大家指点一下，谢谢。

j*n2009-04-06 07:04

16 楼

Just found another thing:
AL32UTF8 ==> CodePage = 1258.
Try this.

some

【在 j*****n 的大作中提到】

: Here is a dummy solution....
: use SSIS to export Oracle data into a text file (Delimited);
: use SSIS to import this text file into your destination table. Better
: predefine your destination table first, otherwise the data type may differ
: when you create it while you import.
: Another solution is that use data conversion component, which could do some
: type transformations but is limited.
: Sure, changing collations is always a pain in the S.
: Let me know which one worked well for you.

B*L2009-04-06 07:04

17 楼

Jackrun, thank you very much for the help.
1258 works, but those random errors are not caused by codepage.I'll try the
import/export text file strategy when I have time.