SOLVED: Unable to retrieve column information / metadata (SSIS)

Here’s another annoying thing that might happen when a stored procedure is too ‘complex’ for SSIS:
"The metadata could not be determined because every code path results in an error..."
"Unable to retrieve column information from the data source. Make sure your target table in the database is available"

SSIS unable to retrieve column information metadata

 

Simply said, it needs help regarding the metadata.
One way to do this is to provide SSIS the necessary metadata information. As shown below:

Introduction

Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.

Example Using WITH RESULT SETS Feature of SQL Server 2012

/*
Example - Using WITH RESULT SETS Feature of SQL Server 2012
*/
EXEC WithResultSets_SQLServer2012
WITH RESULT SETS
(
(
[Employe Name] NVARCHAR(100),
[Employee City] NVARCHAR(20),
[Employee Postal Code] NVARCHAR(30)
)
)
GO

Conclusion

The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.

Read more: http://www.mytechmantra.com/LearnSQLServer/With-Result-Set-Feature-SQL-Server-2012.html#ixzz3nybyEcJU








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s