Delete data from a DB (4 replies)
Llew, there is currently no facility to perform a delete operation.
I think you would need to have a separate operation to perform this outside of IMan (or possibly use a script task).
you can use the script task and perform data base commands there
Hi Mohammadali,
I am trying to use a script task to delete some records in a temp table. I'm having trouble with the syntax (I'm not a programmer). I am wondering if I'm close, or if I should just seek a programmer's assistance.
I'm getting an error message on refresh:
Description: Expected end of statement
Source Line: Dim con As New ADODB.Connection
. ^
Line=3 Pos=8
WHLOCATION is a mapped field in IMAN.
Here is my script
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI10;"
& "Server=ppac-sql-02;"
& "Database=DQuest;"
& "Uid=accpac;"
& "Pwd=xxxxxxx;"
con.Open
sql="DELETE FROM ICEPAC_WHCounts WHERE LOCATION=%WHLOCATION"
con.Execute sql
conn.close
The syntax isn't correct. You're wanting something like (and I stress "like"):
Dim con
con = CreateObject("ADODB.Connection","")
then the rest of your code should work.
One other work around that comes to mind:
You can run SQL commands via a batch file. You could use a file task (I think) to execute a batch file used to delete those records.
After reading a dataset from a DB table and copying that to an archive table, how would I then delete the original dataset?
So I am doing a Select * where archive = 'Y', I am then copying that into a archive table, and then I want to delete where archive = 'Y' from the oroginal table