SSIS: Using stored procedure or function in OLE DB source

Hi all, 

Right now I am doing a project with SSIS 2005 where we are building pretty cool stuff. Well I am not a very experienced SSIS developer so I ran into a very nice problem. I have to transport data from a SQL 2000 database to a SQL 2005 database. The dba created a stored procedure which gives me the incremental data from the SQL 2000 database. So I decided to use this stored procedure as the source of my SSIS package. At the end of the stored procedure there is a select * from #temptable. When I execute this stored procedure in the Query analyzer i see a lot of data divided into multiple columns. When I use this stored procedure as the source in my package and connect this component to the next component I receive a message. Something like do you want to proceed when you don’t have any input columns. Hmm a bit strange because the stored procedure does give me output columns. Well I found a very nice post who clarifies this problem. Check it out:

http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

Because I was also working with SQL 2000 and wanted to check the metadata of the stored procedure I had to change the check query a bit, but this post was very helpful

SQL2000

select o.[name], c.* from syscolumns c
inner join sysobjects o on c.id = o.id
where o.[name] = ‘dbo.usp_COPArbeidsrelatieIncrementeel’

instead of: (SQL2005)

select o.[name], c.* from sys.columns c
inner join sys.objects o on c.object_id = o.object_id
where o.[name] = ‘dbo.usp_COPArbeidsrelatieIncrementeel’