Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Data transfer from AS400 to Sql Server 2000

jogibabajogibaba Member Posts: 1

Hello all,

I want to transfer as well as send data from AS400 to SQL.
Is it possible without using DTS.
Data transfer is not straight forward. Depending on certain conditions
I want to extract specific data. How can I do it

Regards

Harshad

Comments

  • gigsvoogigsvoo Member Posts: 328
    You can use Import Data Wizard from right-click on any database select Import...

    You can also specify the import using the second option "Query..." rather than copy table to table.

    Neo Gigs
    http://communities.msn.com.my/VisualBasicwithNeo

  • xjoaniexxjoaniex Member Posts: 1


    Imports System.Data.Odbc 'To get AS400 Data
    Imports System.Data.SqlClient 'To access SQL Server

    Private Sub GetAS400Data()

    Dim strAS400DataSQL As String

    'Define the SQL statement to extract the data from the AS400
    strAS400DataSQL = " SELECT * FROM AS400Library.AS400File "
    " WHERE Criteria = '799' "

    'Define the ODBC Connection for the AS400 and the AS400 SQL command
    Dim AS400Conn As New OdbcConnection("Driver={Client Access ODBC Driver (32-bit)}; System=AS400Name; Uid=AS400LogonID; Pwd=AS400Password")
    Dim AS400SQLCommand As New Odbc.OdbcCommand(strAS400DataSQL, AS400Conn)

    'Set up the ODBC data adapter and the Data Table
    Dim sdrAS400Adapter As OdbcDataAdapter = New OdbcDataAdapter(AS400SQLCommand)
    Dim TestTable1 As DataTable = New Data.DataTable

    'Define the Connection string for the SQL Server
    Dim strConnCLB As String = "Data Source=SQLServerName;Initial Catalog=SQLDBName;Integrated Security=True"

    Try
    'Open the AS400 connection, Fill the datatable
    AS400Conn.Open()

    'Put AS400 data into a DataTable
    sdrAS400Adapter.Fill(TestTable1)

    'Open the SQL Server Connection
    cnnCLB = New SqlConnection(strConnCLB)
    cnnCLB.Open()

    'Copy data from the DataTable to the SQL Server Table
    Dim Copy As SqlBulkCopy = New SqlBulkCopy(strConnCLB)
    Copy.BatchSize = 1000 'Default Batch size is 1
    Copy.DestinationTableName = "TestTable" 'Name of SQL Server Table
    Copy.WriteToServer(TestTable1) 'Name of DataTable containing AS400 data

    Catch exc As Exception
    'Error handling
    MsgBox(exc.Message.ToString)

    Finally
    cnnCLB.Close()
    cnnCLB.Dispose()
    AS400Conn.Close()
    AS400Conn.Dispose()
    End Try

    End Sub

Sign In or Register to comment.