CODEWITHSUNDEEP
sql-serverdatabaseloggingssisetl
user2138054
user2138054

Reputation: 1

Any suggestions on logging ETL processes?

I need to create logging for ETL processes and a typical process looks something like this.. File arrives on the SFTP server creates a trigger that moves the file and run SQL Agent job. SQL Agent jobs contains multiple steps like run stored procedures and SSIS packages(which calls stored procedures etc)..

Also, there is a lot of interdependency like a procedure is used by multiple processes, a file trigger might trigger multiple ETL. Anyway, you get the picture. I am currently creating a logging database for each tool and I was wondering if anyone here have any suggestions on a simple way to track such dependencies to make sure everything ran as intended.

Upvotes: 0

Views: 301

Answers (1)

SebTHU
SebTHU

Reputation: 1405

I suggest logging the arrival of each file, with its name, date of arrival. And that subsequent steps which use it relate their work to this file in their own logging.

e.g.

FileID FileName   ArrivedAt
2      BLA.txt    2022-07-27 10:00

Then an SSIS package, or proc, which runs as a result of the arrival of this file can log something like "Package DOTHINGS.dtsx ran at [time] using fileID 2".

Upvotes: 0

Related Questions