Re: DTS Import From AS/400 DB2
From: Jeff Turner (jturner_at_discussions.microsoft.com)
Date: 09/23/04
- Next message: rob: "dts imports , records added to table randomly. not sorted as in text file"
- Previous message: Wikus Schalkwyk: "How do I execute a single task in a DTS package?"
- In reply to: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Next in thread: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Reply: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Sep 2004 08:47:02 -0700
Pete, thanks for the response.
I've tried to create a linked server using the the MS OLE DB provider for
DB2 with no success. I found an article that stated you must use MS SNA
Server or MS Host Integration Server to configure the data source for the OLE
DB provider (Microsoft Knowledge Base Article - 218590). Neither of which I
am running or have licensing for. I was able to create a linked server using
the ODBC OLE provider pointing at my Client Access ODBC DSN. However, I could
only return a few rows from my DB2 table(s) (i.e. my query returns 89 rows
from a table that has 62000+ rows using select * from
AS400.[owner].[library].[file]).
Regarding the PK's on my tables most use a key that is not in any sequential
order (i.e. a vehicle VIN number). I had an idea of making a join on the two
tables returning all row from my AS/400 table and only rows from my local
table where the join was equal, then looking for null values in a column from
my local table. I've done this in MS Access before and it works well for
appending new data (SELECT * FROM [AS/400 Table] LEFT JOIN [Local Table] ON
[AS/400 Table].VIN = [Local Table].VIN WHERE [Local Table].VIN Is Null;).
Unfortunately I have to figure out the linked server problem first.
-Jeff
"Peter A. Schott" wrote:
> I was using a linked server to do this. As long as your file has a PK you can
> use to just grab new rows, you should be in good shape. I had to turn on
> Collation Compatibility to get good performance.
>
> Another option would be to use an ActiveX script to set your SQL Query to
> something like:
>
> select * from OPENQUERY(AS400, 'select * from FILE where FILE_PK >
> MAX_PK_LOCAL')
>
> or
>
> select * from AS400.Owner.Library.File where File_PK > Max_PK_Local
>
>
> It can be done without too much trouble and could probably even be done with
> Parameters to the query and Global Variables. Take a look at www.sqldts.com
> for some great examples on all sorts of DTS stuff.
>
> -Pete
>
> "Jeff Turner" <jturner@discussions.microsoft.com> wrote:
>
> > Hello,
> >
> > I am new to SQL Server and apologize if this is the wrong group for this
> > question.
> >
> > I currently run several DTS packages to import data from an AS/400 via
> > Client Access ODBC. The packages are setup to delete all rows from my
> > existing tables then import the data from the AS/400 into them. Ideally I
> > would like to append only new records from the AS/400 rather than importing
> > then entire table(s) from the AS/400 (one of my table has 4.5 million
> > records) every time the package runs.
> >
> > Could anyone point me in the right direction?
> >
> > Thanks,
> >
> > Jeff
>
>
- Next message: rob: "dts imports , records added to table randomly. not sorted as in text file"
- Previous message: Wikus Schalkwyk: "How do I execute a single task in a DTS package?"
- In reply to: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Next in thread: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Reply: Peter A. Schott: "Re: DTS Import From AS/400 DB2"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
