Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AS400 to SQL Server using SSIS Expand / Collapse
Author
Message
Posted Tuesday, September 23, 2008 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 9:24 PM
Points: 4, Visits: 14
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
Post #574316
Posted Tuesday, September 23, 2008 7:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:14 AM
Points: 405, Visits: 1,138
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)
Post #574334
Posted Wednesday, September 24, 2008 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 9:24 PM
Points: 4, Visits: 14
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




Post #574966
Posted Thursday, May 14, 2009 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 2, 2011 11:58 PM
Points: 41, Visits: 170
thanks guys. that did the trick.
Post #716706
Posted Wednesday, July 14, 2010 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 10:34 AM
Points: 1, Visits: 0
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.
Post #952526
Posted Thursday, July 15, 2010 2:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:14 AM
Points: 405, Visits: 1,138
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)
Post #952925
Posted Thursday, July 15, 2010 2:54 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:14 AM
Points: 405, Visits: 1,138
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)
Post #952927
Posted Friday, October 15, 2010 11:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 15, 2010 11:12 AM
Points: 1, Visits: 0
look what I found (late)

http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9
Post #1005388
Posted Tuesday, December 11, 2012 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:44 AM
Points: 1, Visits: 0
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!
Post #1395005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse