September 1, 2011 at 8:57 am
Each
Edit:This should be titled DBCC in a SSIS step. Doh!
I am trying to capture the last Check DB date for each DB on a server. I'm using DBCC to retrieve the information.
Specifically, I have a For Each Each container that is populated with this code
SELECT cast(rtrim([name]) as Nvarchar(128)) FROM sys.databases
I then within the For Each conainer have a SQL Execute task that contains
if object_Id('tempdb..DBInfo_LastKnownGoodCheckDB') is null
CREATE TABLE tempdb..DBInfo_LastKnownGoodCheckDB
(
ParentObject varchar(1000) NULL,
Object varchar(1000) NULL,
Field varchar(1000) NULL,
Value varchar(1000) NULL,
DatabaseName varchar(1000) NULL
)
else
delete tempdb..DBInfo_LastKnownGoodCheckDB
I then have a dataflow task with this as output
DECLARE @SQL varchar(8000)
--Create dynamic SQL to be inserted into temp table
SET @SQL = 'DBCC DBINFO (' + CHAR(39) +db_name(db_id()) + CHAR(39) + ') WITH TABLERESULTS'
--Insert the results of the DBCC DBINFO command into the temp table
INSERT INTO tempdb..DBInfo_LastKnownGoodCheckDB
(ParentObject, Object, Field, Value) EXEC(@SQL)
--Set the database name where it has yet to be set
UPDATE tempdb..DBInfo_LastKnownGoodCheckDB
SET DatabaseName = db_name(db_id())
WHERE DatabaseName IS NULL
--Get rid of the rows that I don't care about
DELETE FROM tempdb..DBInfo_LastKnownGoodCheckDB
WHERE Field <> 'dbi_dbccLastKnownGood'
SELECT
substring(@@SERVERNAME,1,128) AS servername,
DatabaseName,
value
FROM tempdb..DBInfo_LastKnownGoodCheckDB
I then drop the table in a further Execute SQL task.
The thing is, I never get any output from the last snippet of code posted above. If I paste it into a query window it works fine as does the following snippet if I replace the above with this in SSIS:
SELECT
substring(@@SERVERNAME,1,128) AS servername,
Db_Name(Db_ID()) AS DatabaseName,
GetDate() as Date
Any body got a clue as to what is going on? Is DBCC outputing on a different thread/SPID?
Help! :crazy:
Dave J
September 2, 2011 at 3:41 am
This I think is your problem
SET DatabaseName = db_name(db_id())
You're setting every single row in your temp table to database name of master (or whatever conext you run the query under). The name parameter that controls the for each container probably needs to be inserted here on each run
SET @SQL = 'USE ' + quotename(name) + '; DBCC DBINFO ('
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 2, 2011 at 7:24 am
Thanks for the input Perry, but I'm afraid that's not it. I run the code in a For Each container and iterate through the databases that way. The snippet
SELECT
substring(@@SERVERNAME,1,128) AS servername,
cast(Db_Name(Db_ID()) as varchar(128)) AS DatabaseName,
GetDate() as dbccLastKnownGood
works as expected, showing each DBname on the server in the Data viewer window I have put on the data flow. But for some reason DBCC output will not appear in the Data viewer window...
Regards
Dave
September 4, 2011 at 7:56 am
I fixed this by moving the code into the Execute SQL task
DECLARE @SQL varchar(8000)
--Create dynamic SQL to be inserted into temp table
SET @SQL = 'DBCC DBINFO (' + CHAR(39) +db_name(db_id()) + CHAR(39) + ') WITH TABLERESULTS'
--Insert the results of the DBCC DBINFO command into the temp table
INSERT INTO tempdb..DBInfo_LastKnownGoodCheckDB
(ParentObject, Object, Field, Value) EXEC(@SQL)
--Set the database name where it has yet to be set
UPDATE tempdb..DBInfo_LastKnownGoodCheckDB
SET DatabaseName = db_name(db_id())
WHERE DatabaseName IS NULL
--Get rid of the rows that I don't care about
DELETE FROM tempdb..DBInfo_LastKnownGoodCheckDB
WHERE Field <> 'dbi_dbccLastKnownGood'
The dataflow task then just contains
SELECT
substring(@@SERVERNAME,1,128) AS servername,
DatabaseName,
value
FROM tempdb..DBInfo_LastKnownGoodCheckDB
This works but I don't know the difference between running code in a execute sql task or a dataflow task...
Regards
Dave J
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply