Error: unable to retrieve column information from the data source in SSIS

Hi Folks,

I was trying to call a stored procedure from OLE DB Source in SSIS. and i getting this error:

"Error: unable to retrieve column information from the data source"

I wasn't even able to get the columns returned from this stored procedure.

My stored procedure is having a dynamic query i'm building based on passed parameters from SSIS package.

My Stored procedure code:

Declare @sqlStatement as nvarchar(1000)

SET @sqlStatement = ' SELECT * FROM dbo.myTable
WHERE ID IN ('+ @Ids + ' )'
-- For tracing purposes
print @sqlStatement
execute sp_executesql @sqlStatement

To fix this problem, the SSIS when it executes the stored procedure in the design time, it doesn't pass any parameters, with that being said, you have to make sure that your stored procedure is working when you pass NULL values by default. and this was the trick to fix the problem. In my case, when i pass NULL values for my parameter that contains multiple values my SQL query statement is not valid!

I fixed my stored procedure through the following:

Declare @sqlStatement as nvarchar(1000)
IF @Ids is not NULL
SET @sqlStatement = ' SELECT * FROM myTable WHERE ID IN (' + @Ids + ' )'
ELSE
SET @sqlStatement = ' SELECT *FROM myTable'

-- For tracing purposes

print @sqlStatement
-- EXEC @sqlStatement
execute sp_executesql @sqlStatement

After fixing my stored procedure to work with null passed values and my dynamic sql statement is correct i was able to view my columns returned from the stored procedure and it works like a charm!

Hope this tip helps you when you create any stored procedure that is being called from SSIS objects such as OLE DB source or SQL Task.

Thanks,
--ME