Monthly Archives: September 2016

Dynamic SQL in OLEDB source component in SSIS 1

The metadata could not be determined because statement contains dynamic SQL

When we use a batch of T-SQL statements which contains dynamic SQL or uses temporary tables inside, in an OLEDB source component placed in a Data Flow Task of a SSIS package, we get an error during column names extraction because the source component gets failed to fetch the metadata (columns and their data types) for the given T-SQL batch.

In this post, we are going to learn how we can use a dynamic SQL statement or a batch of T-SQL statement which does not expose its metadata directly, in a source component which could be an OLEDB source, or an ODBC source being used to fetch data from the SQL Server.

Follow below steps to generate the error:

  • Create an integration services project in visual studio.
  • Add a Data Flow Task in the package.
  • Create an OLEDB connection