Execute SQL Task not running?

Hi All,

I have a strange problem that I can't find any information about and was hoping someone could help me out.

Some background:

* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.

* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.

* I then move the data from the staging table to a final table.

Here's the problem:

When the SQL Task like this:
WHILE @.@.rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0

is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.

Does anyone have any advice on the possible causes or where to investigate further?

Thanks,
s.

Turn on logging so you can find out what's going on.

-Jamie

|||Hi Jamie,

The log doesn't show anything different between a correct and incorrect run. I think I may have figured out an answer though. I finally figured out that the problem only occurs when I run multiple packages on multiple threads at the same time. Although none of the packages update the same table, perhaps it is something with the @.rowcount that is causing trouble? I was able to fix the problem (as far as I have seen so far) by changing the package executions to use dtexec.

s.