Hi SSIS Experts I have a problem in that I execute the following code within a OLE DB Source to a SQL 2k database. The results are returned when I press the Preview button however when I open the Columns tab I do not get results returned. As you will see from my code I have tried to use both a table var & # table both produce the same results. Any Solutions to this more then welcome set nocount on declare @l_Table_name varchar(255) ,@l_cmd varchar(4000) ,@l_db_name varchar(255) Declare @result table ( SQLInstanceName varchar(255) ,DatabaseName varchar(255) ,TableName varchar(255) ,RecordDate datetime ) -- Create temp table to hold Table data create table #TableList ( SQLInstanceName varchar(255) ,DatabaseName varchar(255) ,TableName varchar(255) ) -- Load list of databases from master into Cursor declare db_name_cursor insensitive cursor for select name from master..sysdatabases where name <> 'Tempdb' -- Exclude Tempdb open db_name_cursor fetch next from db_name_cursor into @l_db_name While (@@fetch_status = 0) begin -- Build select statment to be executed on each database. set @l_cmd = 'use ' + @l_db_name set @l_cmd = @l_cmd + ' insert into #TableList (SQLInstanceName,DatabaseName,TableName) ' set @l_cmd = @l_cmd + ' select @@servername SQLInstanceName,db_name(), name from sysobjects WHERE type = ''U''' -- Exec the command exec (@l_cmd) --print @l_cmd fetch next from db_name_cursor into @l_db_name end -- Clean up Cursor close db_name_cursor deallocate db_name_cursor insert into @Result select *,getdate() RecordDate from #TableList drop table #TableList set nocount off Select * from @result

this links may be help full http://blogs.msdn.com/mattm/archive/2006/11/08/stored-procedures-with-output-parameters.aspx http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=572199&SiteID=1 Regards, Dev

that is a known behavior of the OLE DB Source component. this post has some information about ithttp://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

Tested using your SQL above in its entirety and the following works for me:Just add the following line to the beginning of your SQL statement in the OLE DB Source:SET FMTONLY OFFSo your SQL should look like:SET FMTONLY OFFexec yourStoredProcedure

You ROCK Phil Thanx learned a new command today... Dev

I had same problem with one of my package. I tried this solution. It is working fine. Thanx a lot Phil. It is small command but helped a lot

Hi Phil, For my case, I cannot create a stored procedure nor a function in the source db. I tried including SET FMTONLY OFF in my SQL editor, but it did not work. Please advice

Thanks, it worked for me. You saved my time phil.

Hi Phil, For my case, I cannot create a stored procedure nor a function in the source db. I tried including SET FMTONLY OFF in my SQL editor, but it did not work. Please advice Hi sometimes it doesnt work because Stored Procedures doesnt have metadata in it. So simply you fake it. If your output is A (Nvarchar), B (int), C(Date) write smth like at the beginning of your stored proc: IF 1=0 BEGIN SELECT CAST('' as nvarchar(20)) as A, 1 as B, getdate() as C END BI and ERP Senior Consultant @ Nexum Bogazici | If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

Hi Phil, Thanks a lot. I had a similar problem and your solution worked out for me.

I'm in the same boat. Please tell me there is a workaround for this ridiculous quirk. Help!!!

I've tried SET FMTONLY OFF and the "fake" SELECT. Help!!! SET FMTONLY OFF IF 1 = 0 BEGIN SELECT CAST (NULL AS INT ) AS [Year], CAST (NULL AS INT ) AS [Month], CAST (NULL AS FLOAT ) AS [Transaction Amount], CAST (NULL AS INT ) AS [Transaction Count], CAST (NULL AS FLOAT ) AS [Check Amount], CAST (NULL AS INT ) AS [Check Count], CAST (NULL AS FLOAT ) AS [DirectPay Amount], CAST (NULL AS INT ) AS [DirectPay Count], CAST (NULL AS FLOAT ) AS [Total Amount], CAST (NULL AS INT ) AS [Total Count] END DECLARE @FirstDayof11MonthsAgo as SmallDatetime DECLARE @CurrentDay as SmallDatetime DECLARE @FirstDayof11MonthsAgoString as Char(10) DECLARE @CurrentDayString as Char(10) SELECT @CurrentDay = GETDATE() IF DATEPART(d, @CurrentDay) = 1 BEGIN SELECT @CurrentDay = @CurrentDay - 1 END SELECT @FirstDayof11MonthsAgo = DATEADD(m, - 12, @CurrentDay) SELECT @FirstDayof11MonthsAgo = DATEADD(d, - DATEPART(d, @FirstDayof11MonthsAgo) +1,@FirstDayof11MonthsAgo) SELECT @CurrentDayString = CAST(DATEPART(mm, @CurrentDay) AS varchar) + '/' + CAST(DATEPART(d, @CurrentDay) AS varchar) + '/' + CAST(DATEPART(yy, @CurrentDay) AS varchar) SELECT @FirstDayof11MonthsAgoString = CAST(DATEPART(mm, @FirstDayof11MonthsAgo) AS varchar) + '/' + CAST(DATEPART(d, @FirstDayof11MonthsAgo) AS varchar) + '/' + CAST(DATEPART(yy, @FirstDayof11MonthsAgo) AS varchar) SELECT DATEPART(yyyy, at.postingdt) AS [Year], DATEPART(m, at.postingdt) AS [Month], SUM(at.billingamount) AS [Transaction Amount], COUNT(*) AS [Transaction Count] INTO #Transactions FROM accountTransaction AS at WITH (NOLOCK) WHERE at.postingdt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString AND at.transactionType <> 'Y' GROUP BY DATEPART(yyyy, at.postingdt), DATEPART(m, at.postingdt) SELECT DATEPART(yyyy, pcl.createDt) AS [Year], DATEPART(m, pcl.createDt) AS [Month], SUM(pcl.Amount) AS [Check Amount], COUNT(*) AS [Check Count] INTO #Checks FROM purchaseCardLog AS pcl WITH (NOLOCK) INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId WHERE (pt.PaymentTypeName ='CHECK') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString) GROUP BY DATEPART(yyyy, pcl.createDt), DATEPART(m, pcl.createDt) SELECT DATEPART(yyyy, pcl.createDt) AS [Year], DATEPART(m, pcl.createDt) AS [Month], SUM(pcl.Amount) AS [DirectPay Amount], COUNT(*) AS [DirectPay Count] INTO #DirectPays FROM purchaseCardLog AS pcl WITH (NOLOCK) INNER JOIN v_PaymentType AS pt WITH (NOLOCK) ON pt.PaymentTypeID = pcl.PaymentTypeID INNER JOIN v_purchaseLogStatus AS pls WITH (NOLOCK) ON pls.purchaseLogStatusId = pcl.PurchaseLogStatusId WHERE (pt.PaymentTypeName = 'SETTLE') AND (pls.status = 'Reconciled') AND (pcl.createDt BETWEEN @FirstDayof11MonthsAgoString AND @CurrentDayString) GROUP BY DATEPART(yyyy, pcl.createDt), DATEPART(m, pcl.createDt) SELECT tr.[Year], tr.[Month], ISNULL(tr.[Transaction Amount],0.00) AS [Transaction Amount], ISNULL(tr.[Transaction Count], 0) AS [Transaction Count], ISNULL(ch.[Check Amount], 0.00) AS [Check Amount], ISNULL(ch.[Check Count], 0) AS [Check Count], ISNULL(dp.[DirectPay Amount], 0.00) AS [DirectPay Amount], ISNULL(dp.[DirectPay Count], 0) AS [DirectPay Count], ISNULL(tr.[Transaction Amount],0.00) + ISNULL(ch.[Check Amount], 0.00) + ISNULL(dp.[DirectPay Amount], 0.00) AS [Total Amount], ISNULL(tr.[Transaction Count],0) + ISNULL(ch.[Check Count], 0) + ISNULL(dp.[DirectPay Count], 0) AS [Total Count] FROM #Transactions AS tr LEFT JOIN #Checks AS ch ON tr.[Year] = ch.[Year] AND tr.[Month] = ch.[Month] LEFT JOIN #DirectPays AS dp ON tr.[Year] = dp.[Year] AND tr.[Month] = dp.[Month] ORDER BY 1,2 DROP TABLE #Transactions DROP TABLE #Checks DROP TABLE #DirectPays

I had the same exact situation as Bill, I moved the sql to a stored procedure then executed the SP in the OLE DB Source, it worked for me. I spent 2 days searching forums and trying anything I could think of before I finally found the answer. Hope this helps.