December 28, 2011 at 9:31 am
Hello Friends,
Hope all is well and wish everyone happy holidays!!
I am trying to create an ssis package with OLEDB source and recordset destination. The source uses a cursor as a sql command and below is my script:
DECLARE @Databasename VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT name AS DBName FROM sys.databases where database_id not in ('1','2','3','4','15','16','17','18','19','20')
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Databasename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ['+@Databasename+']
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts, CONVERT (date, GETDATE()) as Date
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID where DPS.avg_fragmentation_in_percent>=25 and index_type_desc<>''HEAP'' and page_count>25
ORDER BY DPS.avg_fragmentation_in_percent DESC'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @Databasename
END
CLOSE TableCursor
DEALLOCATE TableCursor
The issue I have here is when I do a preview I get an error message:
TITLE: Microsoft Visual Studio
------------------------------
The component reported the following warnings:
Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.
Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.
If I ignore the error and hit OK I see the output for a single database. But Since I am using a cursor I thought I would get the results for a few databases. All I am doing here is trying to get the index fragmentation in each database and output it to a record set destination.
And then when I hit on the Columns tab of the OLEDB Source Editor I get the below error which is same:
TITLE: Microsoft Visual Studio
------------------------------
The component reported the following warnings:
Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.
Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.
when I ignore it and click ok I see only one column in the available external columns where I am supposed to see like 7 columns per my query.
Can someone help me resolve my issue.
Thanks a bunch.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 28, 2011 at 9:40 am
Do SSIS packages support cursors??
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 28, 2011 at 10:47 am
The issue here is I need to run this package on every individual database and need to have the output appended to the same recordset destination for each individual database and also have the output overwritten whenever the package is scheduled to run.
Experts I need your advise on this. Thanks so much in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply