Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query from Source not sure how to get into Destination Expand / Collapse
Author
Message
Posted Monday, April 7, 2014 1:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:44 PM
Points: 33, Visits: 320
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
SET @SQL = @SQL + '
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.
Post #1559249
Posted Monday, April 7, 2014 10:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
How about linked server?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1559359
Posted Tuesday, April 8, 2014 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 5,316, Visits: 12,346
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1559500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse