Arline, unfortunately you're not able to have multiple statements in a database reader, furthermore you cannot have a DML Statement (Insert, Update or Delete) in a DB Reader. DB Readers must be a select statement returning a result set.

To achieve this, you would need to have a Script Task which issues the statement.

I will try to find a script and send it to you.

Just checking in on the script?

Arline, apologies....see attached.

What you need to do:

1. Copy the script into a Script task.
2. Alter the SCRIPT_FILE and DB_CONNECTION constants at the top of the script accordingly.
3. Save your SQL script in the location specified by the SCRIPT_FILE constant (we recommend somewhere in the \IMan directory).

Thanks

Hello and thanks for the script- copied it into a script task, altered the constants, and saved the actual SQL to the SCRIPT_FILE location. When I went to run this I got an error:

"Invalid procedure call or argument

Line=17 Pos=0"

which corresponds to "ts.Close" in my file. 

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(SCRIPT_FILE, ForReading, False)
sScript = ts.ReadAll
ts.Close

?

Arline, please find the revised script.

Thanks

Ok, revised script results:

This time I got "File Not Found" Line=17 Pos=0 which corresponds to the same ts.Close element.

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(SCRIPT_FILE, 1, False)
sScript = ts.ReadAll
ts.Close

Other suggestions?

Did you update the SCRIPT_FILE and DB_CONNECTION constants?

Yes and I've tried a couple iterations. SCRIPT_FILE is straightforward, but I get various issues when testing variations on the DB_CONNECTION details, for example:

When DB_CONNECTION is set to:

Const DB_CONNECTION = "Provider=SQLNCLI11;Server=SERVERNAME\SQL2;Database=DBNAME;Uid=USERHERE;Pwd=PASSWORDHERE;"

the error is "File Not Found" Line=17 Pos=0 

Same issue when I set it to:

Const DB_CONNECTION = "Driver={SQL Server Native Client 11.0};Server=SERVERNAME\SQL2;Database=DBNAME;Uid=USERHERE;Pwd=PASSWORDHERE;"

Arline, the likelihood is the file path referred to by the SCRIPT_FILE constant is:

1. Incorrect or;
2. Or cannot be read by IMan due to file permissions.

Thanks

Confirmed- got the permissions resolved and now it's working. Thanks!