March 8, 2016 at 10:00 am
Hi, I have sql 2005 SSIS package. Inside that I have OLE DB source Editor that connects to sql 2005 db and it has one SP that uses dynamic SP.
Now When I open this package using VS 2013, And I need to change this db to sql 2014 db (migrated same db from 2005 to 2014) but then now I am getting this error,
TITLE: Microsoft Visual Studio
------------------------------
Exception from HRESULT: 0xC020204A
Error at Data Flow Task [servera [1863]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description:
"The metadata could not be determined because statement 'EXEC (@CMD)' in procedure 'p_stored_proc_a' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.".
Error at Data Flow Task [usnypdba01 [1863]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Now when I run the same dynamic sql statement in SSMS 2014, it works without issue. then why its not working inside SSIS??
Thanks.
March 8, 2016 at 10:22 am
dallas13 (3/8/2016)
Hi, I have sql 2005 SSIS package. Inside that I have OLE DB source Editor that connects to sql 2005 db and it has one SP that uses dynamic SP.Now When I open this package using VS 2013, And I need to change this db to sql 2014 db (migrated same db from 2005 to 2014) but then now I am getting this error,
TITLE: Microsoft Visual Studio
------------------------------
Exception from HRESULT: 0xC020204A
Error at Data Flow Task [servera [1863]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description:
"The metadata could not be determined because statement 'EXEC (@CMD)' in procedure 'p_stored_proc_a' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.".
Error at Data Flow Task [usnypdba01 [1863]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Now when I run the same dynamic sql statement in SSMS 2014, it works without issue. then why its not working inside SSIS??
Thanks.
Change your OLEDB source from
exec proc
to exec proc with result sets [column definitions]
See here for more detail.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 8, 2016 at 1:55 pm
This is working. Thanks a lot.
March 8, 2016 at 2:31 pm
dallas13 (3/8/2016)
This is working. Thanks a lot.
NP – this one catches everyone.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply