AS400 to SQL Server using SSIS

Question

AS400 to SQL Server using SSIS

reshma_cm

SSC-Addicted

Points: 400
More actions
September 23, 2008 at 7:31 am

#125636

I have data in the AS400 file which should be extracted.
The file would be available on the AS400 or FTP server to be picked up and downloaded to the Sequel Database.
When the data is extracted, the record would be flagged as complete.
The records marked complete could then be purged/cleared as needed.
I am new to SSIS.
1. How can i build the connection between AS400 and SSIS
2. Can i do the data extraction in SSIS itself. If yes how?
3. Can the AS400 data file be also updated from SSIS (When the data is extracted, the record would be flagged as complete.). IF yes how?
Help with examples or references would be highly beneficial.
Thanks a lot

Viewing 9 posts - 1 through 8 (of 8 total)

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

dave-dj SSChampion Points: 11297 More actions · Answer 1

hi

I don't have any example to give you, but as a start point, I would look at setting up an ODBC connection to your AS400, so your going to need IBM AS400 Client Access Express.

This will give you access to the database files and read the files as a source and do your required ETL.

As far as marking it as flagged goes - it may be worth looking at implementing a stored procedure on the AS400 or some sort of macro, and passing and id to the sp or macro.

Hope that's useful to you.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

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

metalray SSCommitted Points: 1647 More actions · Answer 3

metalray

SSCommitted

Points: 1647

May 14, 2009 at 2:09 am

#994316

thanks guys. that did the trick.

rmasoudi SSC Journeyman Points: 85 More actions · Answer 4

I am following your instructions on this forum I have created connection manager and connects fine. when I go to advance editor of the datareader the connection manager that I just created does not show on the list. there is one line in the list "lDbconnection ". there is a warring (with yellow icon) says "Not all the connection managers have been set. set all the connection managers" . When I click on refresh button It says "error at data flow task [dataReader source [66]]: Runtime connection manager with Id "" can not be found" How do I set connection mamanger. Or how to fix this issue? appreciate your help.

dave-dj SSChampion Points: 11297 More actions · Answer 5

Hi,

I never posted back, but I did manage to get FTP to as AS 400 working.

Manually putting a file on the server, you would do something like the following from the command line replace {} values with your own:

- start the ftp session and ping to AS/400.

ftp {YourAS400_IP_ADDRESS}

User: {AS400_Login}

Enter pwd: {AS400_Password}

Add file1 as a new file member to AS/400 file {FileName} in library {LibraryName}

put file1 {LibraryName}/{FileName}.file1

- Close the session

bye

exit

When you need to FTP in AS400, getting to the correct library and file is less intuitive.

Setup your ftp connector to the relevant Server Name and port, with username and password etc.

In your data flow, add an FTP task.

To add a new file member to an AS400 library/file, in the FTP Task set the remote path as follows, again, replacing the {} values with your own.

/QSYS.LIB/{LibraryName}.LIB/{FileName}.FILE

Note!: You will need to upload your file with a file extension of ".MBR". otherwise, AS400 won't pick up the file.

I'll leave you to set the other values. if you need any other help, let me know.

🙂

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 6

Here a useful reference which is what pointed me in the right direction:

http://publib.boulder.ibm.com/html/as400/java/rzahhx08.htm

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

kmd1970 Valued Member Points: 55 More actions · Answer 7

look what I found (late)

http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9

benji.purewal SSC Journeyman Points: 83 More actions · Answer 8

hi,

i've done as described. i can preview the as400 table data when designing my package but whenever i run the package i get an error saying that the as400 file can not be found...?!

help please!