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: Yesterday @ 1:28 PM
Points: 33, Visits: 311
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:32 AM
Points: 5,257, Visits: 12,185
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.
Post #1559500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse