sql to fox pro? will dts work?

[tylerjones]

I've got about 15 different tables in sql server 2000. I have to convert these tables to fox pro tables. I was told to check out dts, which I have, but it just doesn't seem like it will do what we need it to do. The problem is that we need to have a program that runs through a table of our users. Then for each user, pull out the data from the other 15 tables and create tables with just their data in them and then save those tables to a directory on a file server. I have found some info about transforming the sql server data to fox pro through dts, but that requires knowing ahead of time where the destination database is. In our scenario, all we have to work with is a directory on a file server (for each user) that we just need to fill with a copy of the sql server tables. Has anyone done anything like this before? Thanks!

 

[moonshadow]

Ok, bit fiddly this, and you may need to do additional programming (e.g. in VB). How's this for a starter:
1) Have a table which just holds one user name
2) Create queries (or views) that extract the data you want for this particular user, by joining in the tables.
3) Create a DTS package that uses these views (or the SQL typed in directly), to export the data to a fixed directory.
To process the data do this:
1) Set your new table to hold the first user name (use a stored procedure for this)
2) Run the DTS
3) Copy the files from the fixed directory to the user's directory (you may be able to use xp_cmdshell inside a stored procedure and use the user name in the table to set the destination)
4) Update the table with the next user name (use a stored procedure for this).
5) Go back to step 2 until you've processed all the users.

 

[moonshadow]

I've just looked in Books On Line, and two other things that may help you are the Data Driven Query task which looks like you can run some data transforms for each value held in a look up table (e.g. your table of users). You can also use a parameterised query. I'm confident you can do it all within a single DTS package - never done it before myself, but looks like an interesting problem. I'd love to look at it, but haven't the time. Good luck, and let me know how it goes. If it's a seperate file for each table, that will make things a lot easier.