Data Transfer AS400 --> SQL Server (2005) using SSIS package

Question

Data Transfer AS400 --> SQL Server (2005) using SSIS package

dvd_wvr

Old Hand

Points: 314
More actions
May 19, 2008 at 8:50 am

#122152

I'm looking for someone with a lot of SSIS knowledge to give me their opinion on the best way to handle a scheduled data transfer from an AS400 file to a SQL Server table. In a nutshell we have "Master" data that is maintained on our AS400, and we would like to be able to use this data in a SQL Server database as well, so we'd like to have nightly updates from the AS400 to our SQL table to synch up the data. I'm trying to accomplish this with an SSIS package.
I'm new to SSIS packages, and sure I could figure something out on my own, but I'm interested in hearing opinions on best practices so that I might be able to avoid some rookie pitfalls.
I have been able successfully create an Ole DB Source for the AS400 that I want to pull data from.
Thanks!
David

Viewing 11 posts - 1 through 10 (of 10 total)

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

L-JEFF SSC Enthusiast Points: 115 More actions · Answer 1

Did you find the answer to your problem? I am having the same problem and have not found a solution. If you have found the solution could you please share it with me. I am new at sql coding and have not been able to find a solution.

Thanks, LJ

dvd_wvr Old Hand Points: 314 More actions · Answer 2

Hi LJ,

Yes, I was able to work something out. The first thing I had to do was create an Ole DB Source to connect to the AS400. Then in the data flow tab, I added a slowly changing dimension control. This control steps you through a wizard to set it up. One issue I had with it was the fact that some of the data types in my source data (AS400) were different than what I had set up in my SQL table. The solution to this was to add a Data Conversion control between my Data Source control and my Slowly Changing Dimension control. In the Data Conversion control you can change the data types of the incoming fields. You can also give them alias names. AS400 field names can be pretty cryptic.

Hope this helps!

David

L-JEFF SSC Enthusiast Points: 115 More actions · Answer 3

L-JEFF

SSC Enthusiast

Points: 115

May 20, 2008 at 12:22 pm

#818151

I will try it!

Thanks A BUNCH!

LJ

Greg Edwards-268690 SSC-Insane Points: 20667 More actions · Answer 4

We transfer data nightly just as you want to do.

We have a process that runs on the AS400, and when it is complete, we issue a remote command to SQL server. This launches our SSIS packages.

This was done as the initial processing on the AS400 can vary.

It requires installing IBM iSeries Access on your SQL machine (a selective setup to include the ability to issue a remote command), and just a little bit of setup (permissions, and a batch file to launch the SSIS package).

It also creates and sends a log back to the AS400, which can be very helpful in troubleshooting.

Greg E

santoooo SSCommitted Points: 1999 More actions · Answer 5

Hi friends,

For me also the same problem, I am very new to SSIS. we need to transfer data from AS400 to SQL2005 on every week end.

I have installed IBM iSeries drivers,

with that drivers i am able to connnect to AS400,

To transfer data I tried as follows

I placed oledb Source Connection and i gave all the credientials for the connection..

In the SQLCommand Part of the Connetion i placed the query.

when i Clicked on the Preview button it is fetching the records from the AS400. and displaying in System.byte[] array format..

and then placed the Data Conversion to convert the System.byte[] to string.

and finally i placed oledb destination Connnection and i selected the the Table name to which i need to transfer the data.

Now when i select the Execte Task option

it is throwing the following error(s)..!!!

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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.

[DTS.Pipeline] Error: 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.

[DTS.Pipeline] Error: 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.

Please help me, how to transfer the Data From the AS400 to SQL2005.

Thanks in Advance..!!

harshil Mr or Mrs. 500 Points: 590 More actions · Answer 6

We have successfully pulled data from AS400 to SQL Server 2005 using Ritmo .NET Provider for AS400. You can search for it on Google, it's documentation is included in the website. After installing it, you can use this provider by creating a new connection in SSIS.

Hope this helps. Let me know 🙂

pila sara Valued Member Points: 51 More actions · Answer 7

1.Create ADO.NET connection which point to your AS400 Server.

2.Use Data Reader Source [ Select ADO.nety Connection in No.1 ]

3.In first tab after select connection, input select stmt [select * from lib.file]

4.Use Old DB Connection for SQL server

5.Map Column

I've alreday tested it , and it's work fine.

reshma_cm SSC-Addicted Points: 400 More actions · Answer 8

Yes it works perfectly fine with ADO.Net connection manager.

Here below i have briefly explained the issue and explained the solution using ADO.Net

AS400 - SSIS Issue Resolved.

I tried to build a connection between iSeries (as400) and SQL in SSIS using OLEDB Provider IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider.

Testing the connection was successful.

I used the Data Flow Source--> OLE DB source

I used a query to extract the required data from a table. It worked fine and on preview it also retrieved the required data.

howeve on executing the package, it failed giving me the error as follows

"Error: 0xC0202009 at Data Flow Task, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. 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."

This issue can be resolved by using an ADO.Net connection manager with the Data flow source as Data Reader.

Connection Manager

1. Create a new ADO.Net connection Manager

2. Set the Provider to .Net Provider --> ODBC Data Provider

3. Create a DSN (Control Panel -->Administrative Tools-->Data Sources ODBC -->System DSN)

4. In the connection manager for Data source specification select the DSN created. Provide the login information.

5. Test the connection.

Data flow source:

1. Use the DataReader source

2. In Advanced Editor select the Ado.Net connection manager just created.

3. In Component Properties tab --> Custom properties, in SQLCommand specify the required query string (select * from DatabaseName.TableName)

4. Check the column mappings for accuracy

5. Go to Input and Output properties -->Data reader output -->External columns (Select the columns which were of type varchar in the table, they will now be of the datatype UnicodeString (DT_WSTR). This is because by default DataReader reads strings as unicode strings. This implies that in the destination table in SQL these columns must be of type unicode i.e NVARCHAR instead of VARCHAR)

Data Flow Destination

Create the required Data Flow destination and connect the source and destination.

Now the package will successfully extract data from iSeries and update the table in SQL

mcsmangesh Valued Member Points: 61 More actions · Answer 9

Hi,

Here firstly check AS400 connection String . you can add following string in your connection string.

Force Translate=0;

this is useful for forcefully adding data into dataset.

or i have to put connection string here if you got any conversion error or get system.byte() type of data into dataset.following string avoid such problem for AS400 connection

" SQL Package Name=;Sort Language ID=;Default Collection=USERID;Add Statements To SQL Package=True;Hex Parser Option=1;Query Options File Library=;Use SQL Packages=True;Maximum Decimal Precision=31;Data Source=dsnname;Password=password;Sort Sequence=0;Protection Level=None;Convert Date Time To Char=TRUE;Provider=""IBMDA400.DataSource.1"";SQL Package Library Name=;SSL=DEFAULT;Block Fetch=True;Persist Security Info=False;Extended Properties=;Cursor Sensitivity=3;Transport Product=Client Access;Unusable SQL Package Action=1;Catalog Library List=;Trace=0;Maximum Decimal Scale=31;Force Translate=0;Data Compression=True;Sort Table Name=;User ID=userid;Minimum Divide Scale=0"

//mmm

jeyaprakash.t Newbie Points: 9 More actions · Answer 10

Great Post by reshma_Cm,, Thanks a buch!! it saved my day...