October 10, 2012 at 2:46 pm
Hi folks,
I can't decide whether I had a good idea or if this is some kludge to be avoided.
I'm curious if anyone can point out why it would be preferable to log in the package itself.
DECLARE @SessionID int
DECLARE @SSISLogging TABLE (SSISMessage nvarchar(2048))
DECLARE @Package varchar(160)
DECLARE @PackageStatus int
SET @SessionID = 1 --I use other logic here to increment the sessionid each time I call my procedure
SET@ Package = 'MyPackage'
--Call the package while also inserting the package execution logs into table @SSISLogging
INSERT INTO @SSISLogging
EXEC @PackageStatus = xp_cmdshell @Package
--Insert the message and other variables into my final logs table
INSERT INTO MyLoggingTable
SELECT
SSISMessage,
@SessionID,
@Package,
@PackageStatus
FROM @SSISLogging
Constructive or destructive thoughts welcome...
October 10, 2012 at 2:56 pm
Well, it seems like a lot of extra work to capture this. Why is the looging available in SSIS not adequate?
And, xp_cmdshell is one of the features that fall into the category "Just becasue you can doesn't mean you should"
My two cents.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 10, 2012 at 3:01 pm
I'm faced with dozens of deployed packages that do not have logging enabled. So this would replace the need to modify lots of packages.
So I realize that accepted best practice is to do the logging in the package but that didn't happen...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply