DTS from AS 400 to Sql server

Hi DBA's:

I need to transfer data from my source database which is Client Access iSeries AS 400
running DB 400 to my database in SQL Server 2000. Here is what I need to do -

There are files in the Production Enviornment on the DB 400. I need to connect to these files from my SQL Server 2000 database using DTS Wizard and tranfer these files into the tables of my SQL Server 2000 database. Can someone walk me through with the options on the source side of the DTS that I need to choose in order to connect to my AS 400 and get the data.

Thanks in anticipation.I'm assuming you mean DB2, not DB 400...

Look up sp_addlinkedserver in BOL

DTS for 100 text files

Hello DBA's:

I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.

What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

Thanks

VivekTill the time you get a better solution you can use some file concatenation utility to join all those files into a single one and do the data load using DTS.

http://www.jddesign.f2s.com/concat-s.htm|||Does this utility join files OR append them one after the other. I want to append the records not join them? Let me know.|||it appends them|||Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?|||sounds like a job for perl (every admins friend) or heaven forbid vbscript.|||You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||Originally posted by Brett Kaiser
Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?

Brett ... no way you can accomplish this without a cursor ...

I challenge everyone to make a set based solution for the same

LOL ... am working on a similar procedure ... need to uload files daily of the form LocationCD_Extracttype_yyyymmdd.txt ... tab seperated files .. any ideas other than bcp and dts ..|||It is a one-time event. I have no experience in shell scripting, unfortunately.
Originally posted by rnealejr
You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||How about this?

Create 2 DTS packages. An Outer and an Inner Package.

The Outer package has an ActiveX script task the uses the FSO to indentify all the files in a directory, then for each file set the text file connection of the inner package to the path of the text file identified in the Outer package. Then call the Inner package which just has a text file connection and a DB connection with a trasnformdata task.
Loop through all the files repeating the above.

This would be very automated and fairly quick depending on the size of the text files. And actually pretty easy to implement.

Stevesql

DTS- excel option missing

I am a senior DBA using SQL Server 2000.

I just did a clean install of SQL 2000 Personal edition. I am trying to DTS an excel file in but the excel icon is not there!!!!! There are the other excel ODBC sources but they each require me to setup a DSN (not normal!!!)

Anyone had this problem before? I am uninstalling and reinstalling.

-KevinI don't understand...DTS in or out?

Are you using the wizard?

When open the menu option connection, what do you see?|||I am trying to import with DTS.
When I choose the "source" drop down list I am expecting to see a little excel icon with Excel 97-2000 next to it.

I see a bunch of data direct closed icons and the excel treiber , and the WINSQL excel workbook. All of those options require me to define a DSN.

The normal excel option just lets you choose the excel file.

?!?!?!?!?|||So you're in EM and you right click on DTS and SELECT

>>ALL Tasks >>IMPORT DATA...

Then you use the wizard and change the data source drop down...

and you don't see the teal "X" for Excel?

It's half way down the list...

What if you try to build one from scratch?|||No luck.

I am going to try to do it with Informatica.
I applied mdac 2.8 and SP3a.|||Do you have excel installed on the local machine?|||Yes. Thanks for your help. I found another work-around using Erwin instead of SQL Server.
Thanks anyway.

-Kevinsql