I have to migrate an IBM iSeries AS400 database to SS2005/8. I tried to use
Erwin 7.1 tool to Reverse Engineering the iSeries database into a model. Then
I was going to use the model to generate SQL Server table DDL. Then, I was
going to somehow load the data from the iSeries to the SQL Server database.
How, I don't know. There are a total of 5,000 "tables".

Anyway, Erwin keeps crashing after processing several hundred iSeries
"tables." Since Erwin provides little documentation and no forum or unpaid
support on their web site, I have to turn to you.

Does anyone have any idea what the Erwin table limitation is? Has anyone
done something similar? I don't know if bcp is the best way to load the data
into SS, or if SSIS is better. There is about 700 gig of data!

I really need some guidance.

Best Regards,

Michael

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote:

I would try doing it in stages, instead of trying to get Erwin to
swallow it all at once.

5000 "tables" coming from an AS/400, oh you poor man. I had a bit of
experience with bringing data from a package (JE Edwards) running on
AS/400 over to SQL Server. I only had to bring over a relative
handful of "tables" but I found that not one table in this package had
any unique key or constraint. Not one. Some of the data was clean as
could be even without any constraints. On the other hand I never was
able to get a primary key for an invoice item. Since I was feeding a
data warehouse, having a key to merge on was pretty basic, but it
simply was not available.

Another area where you may have an interesting time is data types. I
am guessing that over the course of the conversion process whatever
tool you use will make some pretty questionable data type choices.

As for moving the data, I would be looking at SSIS rather than BCP.
BCP is pretty picky about format. SSIS could also create tables for
you but you would loose things like keys (assuming anything that
advanced was defined to be brought over in the first place). When I
have used DTS/SSIS to create tables I have always had to script them,
edit the script to fix things like data types and make names useable,
and then re-create them. But with 5000 tables I don't know what I
would do. Shoot myself? 8-)

Good luck!

Roy Harvey
Beacon Falls, CT

On Thu, 9 Oct 2008 10:06:07 -0700, Snake
<Snake (AT) discussions (DOT) microsoft.com> wrote: