SQL DTS Package

[marcuscoker]

Hi

I was wondering if anybody could help me with this. I have a Database in SQL server, which is quite large with approx 300 tables. This database is a static database and in order to get the most upto date information I can download the database and recieve it as a zip file with an Access Database. The problem I have is how to find away of getting all these tables in the access database into SQL server. I have tried creating a DTS package in SQL server, but the DTS package appends and will therefore not append exisiting records that have been updated, plus it returns an error message because of duplicate records.

HAs anybody got any ideas of a script that I can run to take all the tables in the access database and load all the data into the existing SQL database. Would I need to drop existing SQL tables first, or Delete the contents of the SQL tables, does anybody have any idea how i might go about doing this

Thanks

Marcus

 

[boblarson]

I'd say you probably want to delete the data in the SQL tables first if you are going to do a full replace of data with the data in Access.

 

[marcuscoker]

Hi Bob

Thats what I was thinking too. Do you have any idea on how to automate this in SQL using either a DTS package or stored procedure>?

 

[boblarson]

You can do a DTS package, but it's a bit more difficult to kick it off (as I found out) because you have to set a reference to the DTS Package dll and your users have to have that available (at least I haven't found a way around that yet).

So, maybe just creating another stored procedure to call would be best.

CREATE PROCEDURE YourSPNameHere AS
Begin
Delete From YourTableNameHere
End
GO

There may be other ways with better syntax, but this works.

 

[pdx_man]

Since the package is already created and the structures are also created, let's re-use the Create Table task that should have been generated.

Nuke the code in there and put in:

TRUNCATE TABLEA
TRUNCATE TABLEB
:
:

 

[FoFa]

You can automate the DTS job by setting it on a schedule to run. We have also allowed applications (like Powerbuilder or VB) to start DTS jobs by kicking off a job on the SQL server that runs it.

 

[marcuscoker]

Thanks

will give these ideas a try