EXTRACT AS400 TABLES TO SQL SERVER using SSIS or SSMS

We have an ERP system on  IBM AS400.  To migrate to to new ERP system, the first requirement is to take the whole 5000 tables from AS400 TO SQL serevr 2012 and then massage and filter the data from SQL serevr.  

What is the best way to do this quickly. The field names can be same on the tables.

We have SQL 2012 standard edition.
narayanhyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
I think Import Export wizard of SQL server will be the shortest way to complete this activity.

Use a Linked Server and then used import export wizard to import tables from AS400 to the SQL
0
narayanhyAuthor Commented:
Thanks Vikas.

I am not able to define the linked server as the source of "import export" operations.

Should I create script  to copy the 1000+ tables ?

I thought I can use the "Copy database" option and use the linked server as the source. Is that possible ?

Thanks again.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to create a Linked Server. Which RDBMS is installed in the AS400 machine? You'll need the Windows client from that RDBMS to be installed in the SQL Server machine and then you'll be able to use the Import/Export wizard.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

narayanhyAuthor Commented:
Thanks Vitor. We have DB2 installed on the AS400. Normally, only DB2 is installed on AS400.  I had type conversion issue when I tried thsi method. Also, I need to automate the whole process with minimum number packages.  Is that possible with import/export function ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you install the DB2 client (need to be the same version of server) in SQL Server machine?
What's exactly the conversion error message?
0
narayanhyAuthor Commented:
I have installed i-series navigator on the server. The error message is about not able to convert the as400 data types. I also installed Microsoft OLEDB for DB2. But it is not working displaying error like "Unable to initialize provider. Parameter is incorrect" error.  

Our sysadmin is creating an instance on the clustered server and when he tries to install OLE DB for DB2, the error says that the server needs to have SQL2008 or more. But, We have installed SQL 2012 on the server ! He is sorting this out.  

I need to transfer 1400 tables of 100Gb+ from AS400 to SQL server ?  How long will this take in SQL server 2012?

My current scripts using linked server (using IBMDA400 PROVIDER) takes 1 hour for 1 GB. Is there a way to speed this up ?    

Thanks in advance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry for the delay.
My current scripts using linked server (using IBMDA400 PROVIDER) takes 1 hour for 1 GB. Is there a way to speed this up ?
Can be a network thing. If so you'll need to have a faster network. Also if there are firewalls between the servers it will take longer.
For the SQL Server side it will be better if you import the data without any index in the tables so the inserts will be faster. After import you must recreate the indexes.
0
narayanhyAuthor Commented:
Thank You Vitor. It seems microsoft OLE provider for DB2 works fatser. I will try to install and update if that improves the speed.
0
narayanhyAuthor Commented:
Sorry for the delay.  Our sysadmin took time to install the MS OLE connector on the standard version of our SQL 2012.

But, it does not appear on the VS2012 in the provider list. SO, I am using IBMDA400 provider.

I have a package extracting 2.3 Million rows (40 columns)  and the size of table is 572 MB.   It takes about an hour. There are no indexes on the destination table.  How much time this will take in a well configured environment ?  The time is same if use a linked server and copy it to destination.

How do I improve it ?  Our configuration is SQL 2012 standard and IBM i-series AS400.  Both source and destination hardware are powerful.  

How do I check if there is a network or firewall issue ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't really tell you how much time will take but if you don't have indexes on the destination then the only thing more that you can do to improve the speed is having a fast direct link so you can avoid gateways and firewalls.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
narayanhyAuthor Commented:
Thanks Vitor. I may have to use Bulk Insert for larger tables.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.