April 7, 2014 at 1:35 pm
I am using SSIS 2012 and am pulling from a Server in cloud that I have read only rights to. after I finish pulling all the Table data I am trying to verify I received everything by doing Count of Rows on all tables.
Here is the Query my boss gave me to get rowcount on the read only server.
DECLARE @TableName VARCHAR(100),
@sql VARCHAR(MAX)
SET @sql = ''
DECLARE cursorDB_Action CURSOR FAST_FORWARD FOR
SELECT Name FROM sys.objects WHERE type = 'U' and SCHEMA_ID = 1
OPEN cursorDB_Action
FETCH NEXT FROM cursorDB_Action INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
select db_name() + '' - Cloud'' as DatabaseName, '''+ @TableName + ''' as TableName, count(*) as CountOfRows, getdate() as DateRan from ' +@TableName + '
UNION'
--EXEC SP_EXECUTESQL @sql
--set @sql = ''
FETCH NEXT FROM cursorDB_Action INTO @TableName
END
CLOSE cursorDB_Action
DEALLOCATE cursorDB_Action
SET @sql = LEFT(@SQL,LEN(@SQL) - 7 ) -- Strip out final UNION
--PRINT @sql
EXEC (@SQL)
My Question is how do I get this result into my destination DB? This does not work as an OLE DB Source inside data flow task it gives me an error about Meta data. Can I somehow make this a "Execute SQL Task" and store output somewhere? I have only done this with one result as a Variable I am not sure how to get all 4 columns and Rows.
April 7, 2014 at 10:47 pm
How about linked server?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 8, 2014 at 8:12 am
Don't like the cursor. Can you run something like this?
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAME
If yes, use it as an OLEDB source in a dataflow. Then you can fire the results where you want.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply