I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table. Appreciate an early feedback. Qadir Syed

Just create a new instance of "OLE DB Command". Pick and choose your connection, and call the command as exec <procedure name>

"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.Is there any advice for executing such stored procedure?

Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata. Code Snippet CREATE PROCEDURE dbo.GenMetadataAS SET NOCOUNT ON IF 1 = 0 BEGIN SELECT CAST(1 as smallint) as Fake -- Publish metadata END -- do real work starting here DECLARE @x char(1) SET @x = (SELECT '1') SELECT cast(@x as smallint) RETURN

jaegd - thanks!!!

It does not work If there are more than one rows are coming out of stored procedure.

I got it solved on my end by replacing all temporary tables by temporary variables.

Please give an example of what doesn't work for you.

In the above post it should be table variable not temporary variable.Conclusion:It was table variable that Used instead of temporary table.

Hey try with the example belowCREATE PROCEDURE dbo.GenMetadataASSET NOCOUNT ONCREATE TABLE #test([id] [int] NULL,[Name] [nchar](10) NULL,[SirName] [nchar](10) NULL) ON [PRIMARY]INSERT INTO #testSELECT '1','A','Z' union all select '2','b','y'select id,name,SirName from #testdrop table #testRETURNPlease let me know the result.

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline. Code Snippet IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL DROP PROCEDURE [dbo].[GenMetadata] GO CREATE PROCEDURE [dbo].[GenMetadata] AS SET NOCOUNT ON IF 1 = 0 BEGIN -- Publish metadata SELECT CAST(NULL AS INT) AS id, CAST(NULL AS NCHAR(10)) AS [Name], CAST(NULL AS NCHAR(10)) AS SirName END -- Do real work starting here CREATE TABLE #test ( [id] [int] NULL, [Name] [nchar](10) NULL, [SirName] [nchar](10) NULL ) INSERT INTO #test SELECT '1', 'A', 'Z' UNION ALL SELECT '2', 'b', 'y' SELECT id, [Name], SirName FROM #test DROP TABLE #test RETURN GO

Hi Thank you very much..It is now working fine for the changes you suggested..

Hello,With the procedure you have told,SSIS package able to detect output of the stored procedure.But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.First dataset having 1 row and this is as a result of First select statement. Second dataset bcz of our actual select query.So SSIS chooses first dataset,So returns only one row having Null values for all columns.How to overcome from this?

Posta sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

what if you want to execute a dynamic script i.e. Code Block create procedure etl.executeScript @scriptId int, @sessionId varchar(50)as set nocount on declare @script nvarchar(max) select @script=replace(script,'SESSION-ID',@sessionId) from etl.script where id=@scriptId exec sp_executesql @scriptreturnexec etl.executeScript 1, 'my session'basically every script has an id, and a sessionid. ole db command can't pick the meta data coming out of this stored procedure.in the etl.script table, usually scripts meta data don't change, mostly it's the where class that changes.when executing the ssis package, script id remains the same, the only thing that change is the sessionid. so for the engine meta data never changes, only the body does. for example a tipical script would look likeselect fundid, fundname, descrptinfrom dbo.currentsessionwhere sessionid='SESSION-ID'so the etl.executeScript stroed procedure replace the sessionid with the passesed sessionid.any help as to how i can get ole db source to show up the meta data in the columns section.cheers

I realize this probably isn't the answer you are looking for, but why not just put in the actual SQL, with a parameter for SessionID? What's the point of using a dynamic script if the metadata will always be the same?

hi jewlch.basically i'm trying to avoide opening up SSIS for every scrpt change. i only want to open SSIS if there's a meta data change to fix it up. also as you probably know there's a sql command text limit of 4000 characters. sometimes my queries get a little big, (trust me they are optimised to the best extent, but some of them have derived queries with in, so no point in breaking up really)

Hey I am facing another problem that I have multiple #tables and multiple select statement in Store proce and SSIS says # table does not exist. As suggested by him if I put Return any where in between it will come out without executing multiple record set. Please advice

Try putting a non-executing SELECT statement at the beginning of the procedure, as jaegd showed earlier in this thread.

I've been having similar issues which jaegd's example looked like it would solve. It worked when I tested with a SQL server simple example of a proc using a temp table but failed for a Sybase data source with a more complex proc. I cannot get this workaround to work with Sybase as the OLEDB source, even with a very simple proc example? Are there additional complications with using Sybase? Many thanks, Chris

Hi I still can't see the outpout columns in OleDb Command Output Columns , how can i get the output columns. I want to use them to insert in ole db destination here is my sp: create PROCEDURE [dbo].[GetData] ( @user varchar(50) ) AS set nocount on -- Publish metadata for ssis if 1=0 begin select '' x, '' y, '' z end declare @user_tmp table ( x varchar(max), y varchar(max), z varchar(max) ) insert into @user_tmp select 'x1' x, 'y1' y, 'z1' z select distinct * from @user_tmp set nocount off

i had the same problem--there is two things u need to do . u need to alter your stored procedure and add is just after the begin statment.SET NOCOUNT ON SET kkkkFMTONLY OFF

Here is what u need to do alter your stored procdures and just after the Begin statement do this SET NOCOUNT ON SET FMTONLY OFFkkkk

Hi jaegd,Thanks a lot. I have defined your suggestions in my stored procedure and my package is working fine.Regards,Suresh

Hello All, I have a stored procedure which returns a a lot of rows . I need help in executing the stored procedure and saving the output to a flat file destination. Please let me know how to do this.

Here is what u need to do alter your stored procdures and just after the Begin statement do this SET NOCOUNT ON SET FMTONLY OFF kkkk I read at multiple places by using the "SET FMTONLY OFF" will cause it to execute 5 times. Here's an example reference. http://ssisblog.replicationanswers.com/2007/11/13/coping-with-no-column-names-in-the-oledb-data-source-editor.aspx I recall there's an actual blog or forum post describing this. However, I'm unable to find it anymore.

With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline. Code Snippet IF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL DROP PROCEDURE [dbo].[GenMetadata] GO CREATE PROCEDURE [dbo].[GenMetadata] AS SET NOCOUNT ON IF 1 = 0 BEGIN -- Publish metadata SELECT CAST(NULL AS INT) AS id, CAST(NULL AS NCHAR(10)) AS [Name], CAST(NULL AS NCHAR(10)) AS SirName END -- Do real work starting here CREATE TABLE #test ( [id] [int] NULL, [Name] [nchar](10) NULL, [SirName] [nchar](10) NULL ) INSERT INTO #test SELECT '1', 'A'%