Variable - Capture Running Count of Errors -- sql server dts

Nathan
1/25/2007 10:32:29 AM

Hello,

I'm wondering if anyone knows an easy way to create a variable in SSIS
2005 that captures a running count of the errors occured on a given run
of a package. So far, I've tried setting up "SSIS log provider for SQL
Server", then creating an Execute SQL Task:

Input Parameter: System::ExecutionInstanceGUID
Result Set: errorCount
SQLStatement:
SELECT COUNT(*) AS errorCount
FROM sysdtslog90
WHERE (event = 'OnError') AND (executionid = ?)

The SQL Task works without the "AND (executionid = ?)". For a given
run, does the [executionid ]in the sysdtslog90 match the
"System::ExecutionInstanceGUID" variable?

Allan Mitchell
1/26/2007 10:35:40 AM

You know what I would do? I would have a variable scoped to the package
and I would capture the errors and increase the variable value using
event handlers.

From BOL

Column Name: executionid
Data type: uniqueidentifier
Description: The GUID of the execution instance of the executable that
generated the logging entry

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]

Nathan
1/29/2007 8:24:40 AM

Thanks Allen! I guess I was just making it a lot harder than it had
to be. I just added a Script Task in the OnError Package Event that
incriments the errorCount value:

Dts.Variables.Item("User::errorCount").Value =
CInt(Dts.Variables.Item("User::errorCount").Value) + 1

Cheers,

Nathan

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Variable - Capture Running Count of Errors