December 28, 2011 at 2:22 pm
Hello Friends,
Hope all is well and wish everyone happy holidays!! I hope I am in the right place now... 🙂
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.
Do SSIS packages support cursors??
The other 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
December 28, 2011 at 2:25 pm
I know changed my package to a Execute SQL Task and removed the cursor out completely and included just the SQL query to pull the data out. I am using a foreach loop cotrol flow task to pass the database names but I am getting errors. Below is what I did so far.
In the Execute sql task editor I set up the connection type, connection and source type, I put the ResultSet as FullResultSet and in SQL Statement I am using the below code:
use [?]
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
and made the ByPassParse false.
In the parameter mapping I created a user variable User::DatabaseName of type Varchar.
I am now adding a ForEach Loop Editor and In the Collection tab I selected ForEach From Variable Enumerator and in the Enumerator Configuration I created a new user variable called User::DBName
Now I want to pass a list to database names on which I want the package to be executed.
So In the expressions of the collections tab, I selection a property called variablename and I browsed in the expression. I went to variables folder, selected the User variable that I created which is User::DBName and in the bottom at the expression text I entered all the database names seperated by a column and I get the error that I pasted above. Is this not the place to pass the parameters?
“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 3:07 pm
I am attaching the document with the flow of screenshots and the error I am facing when entering multiple variable values. Hope this will clear you what I am exactly looking for.
Thanks
“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 3:33 pm
I would do like this.
using FOR EACH LOOP
now create a variable to hold the sql
design the sql in the expression of the variable using all the variables being passed from FOR EACH LOOP.
Preview the sql in the expression editor and click Ok.
In OLEDB Source Editor, Select "Sql Command from Variable" in Data Access mode.
In the next drop down select the sql variable.
Hope this helps out. If you were succussed implementing this just drop a note.
December 28, 2011 at 3:48 pm
Sapen (12/28/2011)
I am attaching the document with the flow of screenshots and the error I am facing when entering multiple variable values. Hope this will clear you what I am exactly looking for.Thanks
You Cannot assign values like that.
December 28, 2011 at 4:09 pm
Hi
First of thanks for the reply.
Yeah I tried the method which you already proposed. It didnt work. All I got was the error that I pasted earlier. Hence I moved to running it from EXECUTE SQL Task instead of OLEDB Database Source.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 30, 2011 at 9:01 am
Can you please see if this link below helps for using ForEachLoop
http://www.codeproject.com/KB/database/foreachadossis.aspx
and follow the steps of creating the sql in a variable.
December 31, 2011 at 6:39 am
Thanks Alburaj. Do you have any urls for using the for loop with a collection of input parameters, like in my case i will be using the same parameter with a bunch of different values...i meant different database names have to be passed and then the package has to be run on each individual database.
Also I want to output the results to a recordset for each database and then once the package has been executed the results from each database has to be collected for all databases. Also whenever I run the package I want to shred the previous output for all the databases and accepts only new outputs.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 19, 2012 at 8:54 am
I also faced a requirement today similar to what you had... and I was successful in implementing all the ideas I told and the url on how to use FOR EACH loop also helped me. I wonder what is going with you.
January 19, 2012 at 9:19 am
I also faced a requirement today similar to what you had... and I was successful in implementing all the ideas I told and the url on how to use FOR EACH loop also helped me. I wonder what is going with you.
I filtered out the databases I want based on the database_id in the query that I posted earlier and have the output generated to a textfile. So I get an email with this text file attached whenever the package is run. All the previous data gets shred whenever the package is run.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply