SSIS Ole DB Source Editor - Error: No Column Information Returned by SQL Command

Question

The Ole DB Source Editor was functioning correctly with the Data Access Mode set to SQL command, using a query that included a parameter.

Sample Query:

SELECT * FROM view_CUSTOMER_DATA WHERE ACTIVE = 'Y' and LAST_SHOPPING_DATE = ?

A parameter with a date is mapped in Parameters.

However, for the past 3-4 days, an error has been encountered: "No column information was returned by the sql command." When using the same query with a date value, it returns results. If the parameter is removed, the column information is returned correctly.

Recent changes involved adding change history comments in the View definition, positioned belowthe CREATE OR ALTER statement but before the SELECT statement. These changes were made to approximately 25 views. Despite removing all comments, the issue persists, and now the SSIS packages are not functioning.

Searching for solutions revealed an option to use SET FMTONLY OFF, but this is deprecated in newer SQL Server versions, and I am currently using SQL Server 2022 where it still works.

Any suggestions for a workaround with examples would be appreciated.

Answer 1

It sounds like a frustrating issue! The error "No column information was returned by the SQL command" can be tricky, especially when dealing with parameters in SSIS. Here are a few potential workarounds you can try:

1. Use SET FMTONLY OFF: Although deprecated, it can still be used in SQL Server 2022. You can include it at the beginning of your SQL command to ensure metadata is returned:

      SET FMTONLY OFF;
   

SELECT * FROM view_CUSTOMER_DATA WHERE ACTIVE = 'Y' and LAST_SHOPPING_DATE = ?

   
1. **Use WITH RESULT SETS**: This option allows you to explicitly define the expected result set. This can help SSIS understand the metadata:

   ```sql
   SELECT * FROM view_CUSTOMER_DATA WHERE ACTIVE = 'Y' and LAST_SHOPPING_DATE = ?
WITH RESULT SETS (
    (Column1Name DataType, Column2Name DataType, ...)
);

1. Dummy SELECT Statement: Add a dummy SELECT statement that will never execute but provides the necessary metadata:

      IF 1 = 0
   

BEGIN SELECT CAST(NULL AS DataType) AS Column1Name, CAST(NULL AS DataType) AS Column2Name, ... END SELECT * FROM view_CUSTOMER_DATA WHERE ACTIVE = 'Y' and LAST_SHOPPING_DATE = ?

   
1. **Check View Definition**: Ensure there are no syntax errors or issues in the view definition that might be causing the problem. Sometimes, even minor issues can lead to metadata retrieval problems.

1. **Validate External Metadata**: Set the `ValidateExternalMetadata` property of the OLE DB Source to `False`. This can sometimes bypass the metadata validation step that causes the error.

1. **Recreate the OLE DB Source**: Sometimes, simply deleting and recreating the OLE DB Source component in SSIS can resolve unexpected issues.

These steps should help you troubleshoot and resolve the issue. Let me know if you need further assistance!

Jonathan