Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AS400 to SQL Server using SSIS


AS400 to SQL Server using SSIS

Author
Message
reshma_cm
reshma_cm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
dave-dj
dave-dj
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 1149
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
reshma_cm
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
metalray
metalray
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 170
thanks guys. that did the trick.
rmasoudi
rmasoudi
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
dave-dj
dave-dj
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 1149
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
dave-dj
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 1149
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
kmd1970
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
look what I found (late)

http://www-01.ibm.com/support/docview.wss?uid=nas2a802b6eb29d32cac8625726c0041efc9
benji.purewal
benji.purewal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search