how to do ssis export to it overwrites an access table 1

[NuJoizey]

I'm trying to set up a weekly 'dump' to an mdb file. I set up an SSIS package, saved it and scheduled it. The package basically takes tblOrders in sql2005 and dumps it into a table called tblOrders in Access.

This works great the first time, but on subsequent runs, it errors out saying the table already exists.

Since I am still rather green with SQL server, i know that I probably must script this somewhere, but don't know where to go. Can you guys/ladies point me in the right direction? or point me to a good tutorial?

 

[AlexCuse]

Isn't there a 'drop and recreate' option, or something like that?

Really, you will just need to run a DROP TABLE tblOrders command against your access connection (from a SQL Task), and then it should run without a hitch.

FYI, there is an SSIS forum here: forum1555

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[NuJoizey]

if only. I'm in win2k server running mssql 2000, and i found a an applet under DTS packages that lets you do this somewhat graphically - i don't even know if it has a name, but fooling around with it for an extended period of time yeilds the discovery that you can enter the sql command DELETE FROM tblOrders from the target destination before you tell it to dump the SELECTed records into that target. This has taken me forever to figure out, but I didn't see any other immediately apparent way of doing this.

Of course, please let me know if there is another simpler way, becuase I find the applet not really that intuitive.

 

[AlexCuse]

Ah, so you shceduled a DTS package then ;-)

Delete from, then inserting to the table ought to work just fine. When you go back into your package in design view does it show a SQL Task (little cylinder with red arrows) and then a data pump task (gray line from SQL Server source to Access Destination)?

Anyways, glad you got it working

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[NuJoizey]

why yes it does!. Thing is though, i was trying to add to the logic of my dts package using strictly the interface, and not the wizard - and alhtough I figured it out to the extent that I needed to, i found it kinda hard to use.

Thank you for the followup though, the discussion is very helpful.

 

[AlexCuse]

Yeah the DTS design interface leaves a bit to be desired (IMO). It is just not that intuititve (but after a while, you get used to it)

If you get a chance, check out SQLDTS.com. Reading around on there will help you get familiar with all the tasks and what they do.

Good Luck!

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[NuJoizey]

great. thanks for the link. didn't know there was a whole site dedicated to just dts