November 7, 2017 at 2:16 am
I have an SSIS package which has a sql task, foreach loop and data flow task inside the foreach loop.
SQL task - gets list of servers
General:resultset:full result set.
result set--> user::serverlist
foreach loop - collection-->ENUMERATOR-->fOR EACH ado ENUMERATOR.
ADO object source variable:User::serverlist
dataflow task:oledbsource--->oledb destination
variable mappings:->user::currentserver -->0 (index)
The package runs without any failure but not data comes into the destination sql table.
When I double click the data flow task while it is running the oledb source and oledb destination do not change color so obviously it looks like this is not getting executed.
Retain same connection -true (oledb source and destination properties)
delay validation - true (oledb source and destination properties)
When I click on preview in ole db source I find the results in the preview window.
The sql query :
IF EXISTS ( SELECT *
FROM [tempdb].[dbo].[sysobjects]
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))
DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]
GO
CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](
[DatabaseName] [nvarchar] (100) NULL,
[ObjectName] [nvarchar] (100) NULL,
[Index_id] INT,
[indexName] [nvarchar] (100) NULL,
[avg_fragmentation_percent] float NULL,
[IndexType] [nvarchar] (100) NULL,
[Action_Required] [nvarchar] (100) default 'NA'
) ON [PRIMARY]
DECLARE @dbname varchar(1000)
DECLARE @sqlquery nvarchar(4000)
DECLARE dbcursor CURSOR for
SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
OPEN dbcursor
FETCH NEXT FROM dbcursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlquery = '
USE [' + @dbname + '];
IF EXISTS
(
SELECT compatibility_level
FROM sys.databases
WHERE
name = N'''+ @dbname +'''
AND compatibility_level >= 90
)
BEGIN
INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]
(
DatabaseName
, ObjectName
, Index_id
, indexName
, avg_fragmentation_percent
, IndexType
)
SELECT
db_name() as DatabaseName
, OBJECT_NAME (a.object_id) as ObjectName
, a.index_id, b.name as IndexName
, avg_fragmentation_in_percent
, index_type_desc
FROM
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON
a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE
b.index_id <> 0
AND avg_fragmentation_in_percent <> 0
END;'
EXEC sp_executesql @sqlquery
FETCH NEXT FROM dbcursor
INTO @dbname
END
CLOSE dbcursor
Deallocate dbcursor
-- Update the action require for item with average fragmentation value >30 to "Rebuild"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Rebuild'
WHERE avg_fragmentation_percent >30
GO
-- Update the action require for item with average fragmentation value >5 & <30 to "Reindex"
UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]
SET Action_Required = 'Reorganize'
WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5
GO
-- Show the index fragmentation result
SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]
ORDER BY databasename
November 8, 2017 at 4:02 am
Are you saying that the data flow task goes green, but the source and destination don't? Is there anything in the package log that shows the data flow task starting and finishing? Does anything happen in between?
Try changing your list of servers so that there's only one server in it. Do you still get no rows transferred? What happens if you manually run the query on that server?
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply