March 8, 2012 at 8:55 pm
Hello All,
Can I log the results of DTEXEC to a local table? When I run it from SSMS the results are returned so I am hoping to capture this to a table I specify. I'd prefer not to configure the package itself for logging. My thinking is if it is returned to SSMS why can't I capture this there.
Thanks if you can help...
March 9, 2012 at 2:55 am
I haven't tried it before, but with the /L switch you can associate a logging provider and with the /Rep switch you can set the logging level. It's worth a try.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 10:37 am
Purely using T-SQL? You would need to use xp_CmdShell and pipe the output into a table :sick:
You could look at the option Koen provided, and I'll add one more to the mix:
DTLoggedExec is a tool that allows you to run a Sql Server Integration Services (SSIS) Package producing a full and detailed logging informations of execution status and package runtime data, including dataflow profiling information. In brief it allows to fully log and instrument package execution.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 12:00 pm
xp_CmdShell is already in the mix....
DTLoggedExec is what I really want but I usually refrain for 3rd party software.
So I can do something like the below.
I'd prefer to only have one INSERT but I don't know if it's possible to insert the contents of xp_CmdShell and my @SessionID variable with the same insert.
DECLARE @ImportPackage varchar(160)
DECLARE @ImportPackageString varchar(170)
DECLARE @ImportPackageStatus int
DECLARE @SessionID int
SET @SessionID = 1000000
--Set variables
SET @ImportPackage = 'MyPackage'
SET @ImportPackageString = 'dtexec /sq ' + @ImportPackage
CREATE TABLE #SSISLogging
(
SSISMessage varchar(2000)
--,SessionID int
)
INSERT INTO #SSISLogging
--Execute dynamic SQL by using EXEC; Set @ImportPackageStatus to the return variable of the package
EXEC @ImportPackageStatus = xp_cmdshell @ImportPackageString
CREATE TABLE #SSISLoggingFinal
(
SSISMessage varchar(2000),
SessionID int
)
INSERT INTO #SSISLoggingFinal (SSISMessage, SessionID)
SELECT
SSISMessage,
@SessionID
FROM #SSISLogging
SELECT * FROM #SSISLoggingFinal
DROP TABLE #SSISLoggingFinal
SELECT * FROM #SSISLogging
DROP TABLE #SSISLogging
March 13, 2012 at 12:44 pm
How about something like this?
IF OBJECT_ID(N'tempdb..#SSISLogging') IS NOT NULL
DROP TABLE #SSISLogging;
DECLARE @ImportPackage VARCHAR(160),
@ImportPackageString VARCHAR(170),
@ImportPackageStatus INT,
@SessionID INT,
@sql NVARCHAR(MAX)
CREATE TABLE #SSISLogging
(
SSISMessage VARCHAR(2000),
SessionID INT NOT NULL
)
--------------------------------------------------------------------------------------------------------
--Set variables
SET @SessionID = 1000000
SET @ImportPackageString = 'dir C:\'
-- add default to temp table
SET @sql = N'ALTER TABLE #SSISLogging ADD CONSTRAINT [df_#SSISLogging.SessionID] DEFAULT (' + CAST(@SessionID AS VARCHAR(20)) + N') FOR SessionID;'
EXEC(@sql)
-- call xp_CmdShell, capturing results to table
INSERT INTO #SSISLogging
(
SSISMessage
)
--Execute dynamic SQL by using EXEC; Set @ImportPackageStatus to the return variable of the package
EXEC @ImportPackageStatus = sys.xp_cmdshell
@ImportPackageString
-- remove default from temp table
ALTER TABLE #SSISLogging DROP CONSTRAINT [df_#SSISLogging.SessionID]
--------------------------------------------------------------------------------------------------------
--Set variables
SET @ImportPackageString = 'dir C:\'
SET @SessionID = 2000000
-- add default to temp table
SET @sql = N'ALTER TABLE #SSISLogging ADD CONSTRAINT [df_#SSISLogging.SessionID] DEFAULT (' + CAST(@SessionID AS VARCHAR(20)) + N') FOR SessionID;'
EXEC(@sql)
-- call xp_CmdShell, capturing results to table
INSERT INTO #SSISLogging
(
SSISMessage
)
--Execute dynamic SQL by using EXEC; Set @ImportPackageStatus to the return variable of the package
EXEC @ImportPackageStatus = sys.xp_cmdshell
@ImportPackageString
-- remove default from temp table
ALTER TABLE #SSISLogging DROP CONSTRAINT [df_#SSISLogging.SessionID]
SELECT * FROM #SSISLogging
--------------------------------------------------------------------------------------------------------
-- repeat above blocks as many times as needed, adding new default value before calling xp_cmdshell again
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2012 at 4:17 pm
Interesting! I never would have thought of that. Thanks for you help.
March 13, 2012 at 4:23 pm
Anytime, happy to assist 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply