Many people like to use stored procedures as the source of data for the SSIS pipeline. In theory there is nothing wrong with that however it does have its complications. Depending on the way the stored procedure is written, it may or may not be suitable to be used in this way. Namely, it is possible to preview the data being returned from a stored procedure but when switching to the 'Columns' tab of the OLE DB Source Adapter you do not see any columns.
The reason is fairly simple. At design-time SSIS interrogates the OLE DB Provider that sits between itself and the source of data and requests the metadata of the data that would be returned from the query. It is the job of the OLE DB Provider to interrogate the underlying source and pass the metadata back to SSIS. However, stored procedures do not possess interogatable metadata - you can confirm this by executing:
select o.[name],c.*
from sys.columns c
inner join sys.objects o
on c.object_id = o.object_id
where o.[name] = '<your_stored_procedure_name>'
This presents a problem for the OLE DB Provider. It needs to know the metadata but it can't get it from the system catalog so what does it do? To all intents and purposes it has to make a "best guess" which essentially means it takes the metadata of a select statement at the top of the stored procedure. However, if no such query exists then it simply can't do it. Here is a stored procedure that exhibits this very problem:
1 create procedure test_proc
2 as
3 begin
4
5 --Table created at the top of the procedure
6 create table #tmp
7 (
8 col1 varchar(20),
9 col2 varchar(50)
10 )
11
12 --Put some values in
13 insert into #tmp
14 values ('abc','xyz')
15
16 --Only now do we return some data
17 Select col1,col2
18 From #tmp
19 END
Go ahead and create this stored procedure and try and use it in an OLE DB Source Adapter. You won't be able to.
At this point I want to refer you to an excellent blog post from Adam Machanic called "Stored procedures are not parameterized views". It explains in excellent detail about this inherent problem with stored procedures. As Adam puts it "Stored procedures expose no explicit output contract". That's another way of saying what I said above about stored procedures not possessing any metadata. If you read through to the end of Adam's article he states that table-valued user-defined-functions (UDFs) exist as a workaround to this problem. I took Adam at his word and changed the above stored procedure into a table-valued UDF. Here is that function - it returns exactly the same data as the stored procedure:
1 create function test_func ()
2 returns @tmp table ( --set up our table-valued UDF
3 col1 varchar(20),
4 col2 varchar(50)
5 )
6 as
7 begin
8 --Put some values into table
9 insert into @tmp
10 values ('abc','xyz')
11
12 --and return the data
13 return
14 end
Now execute this query again:
select o.[name],c.*
from sys.columns c
inner join sys.objects o
on c.object_id = o.object_id
where o.[name] = 'test_func'
Sure enough, the metadata (or 'contract' in Adam's terminology) is available for all to see and we can now use this function in our OLE DB Source adapter using the following query:
select col1, col2 from test_func ()
without any problems.
This problem seems to have vexed many SSIS developers in the past and I've been meaning to write this explanation for ages now - I just never got round to it.
-Jamie