Tie together custom logging and SSIS logging?

I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.

The only variable I see is ExecutionInstanceId, which is also a GUID.

In the sysdtslog90 table, I see sourceid and executionid, both GUIDS.

When I run my package, the system::PackageExecutionId does not match either sourceid or executionid.

Anyone know whats up

Answer this question

Tie together custom logging and SSIS logging?

ashishbhatt12
http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
JVP
Google came up with this...

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
Javicho_
I was able to get this to work just like the article showed.

System::ExecutionInstanceGUID is the PackageExecutionId that the article refers to.

At the beginning of a package, I log the package execution id to custom table with some other package level metadata. I am then able to run a query like this:

select l.message from etl_package_execution e
inner join sysdtslog90 l
on e.PackageExecutionId = l.executionid

which would return something like this (depending how logging is configured):

(2 rows)
Beginning of package execution.
End of package execution.

System::SourceID corresponds to the unique ID assigned to each Package and Task. So, if you wanted all the dts logs for a certain version of the package, you could do a query like this:

select l.message from etl_package_execution e
inner join sysdtslog90 l
on e.PackageId = l.sourceid
where e.Version = '1.1' and e.PackageName = 'Cln_RunDataScreens'
Ash Sharma

Could you please give the link of this article

Thanks a lot
Tie together custom logging and SSIS logging?

Answer this question