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.myTableWHERE ID IN (‘

+ @Ids + ‘ )’
– For tracing purposesprint @sqlStatement

– EXEC @sqlStatementexecute 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 NULLSET @sqlStatement = ‘ SELECT * FROM myTable WHERE ID IN (‘ + @Ids + ‘ )’ELSESET @sqlStatement = ‘ SELECT *FROM myTable’


– For tracing purposesprint @sqlStatement

– EXEC @sqlStatementexecute 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

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>