Data integration between IBM AS400 to SQL Server database

sql sql-server db2 ibm-midrange data-integration
ghoston3rd picture ghoston3rd · Feb 23, 2016 · Viewed 8.1k times · Source

I'm a web developer that has been tasked with creating some sort of mechanism for moving data from an IBM AS400 to a SQL server. Unfortunately, linked servers are out of the question in this case as the SQL Server is just Standard Edition (db2 providers not available in this version) and the AS400 server is on a separate server. I've researched adding some sort of trigger on the AS400 table that calls a web service that would insert data into the SQL server, but that doesn't seem like the best method. Does anyone have any suggestions on the process to get the data from the AS400 to the SQL Server when it is committed to the AS400?

Answer

Fuzzy picture Fuzzy · Feb 23, 2016

This solution assumes you are familiar with SQL Server Integration Services (SSIS):

Connection to AS400

  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)

Answer sourced from www.sqlservercentral.com/Forums

Related questions

Query validation using C#

I am looking for a query validator in C#, which allows me to parse the SQL text from a textbox and verify whether it's correct or not before sending it for execution (MS SQL or DB2 queries).

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More