SSIS Error: Invalid object name - but object exists and query runs in SSMS

5
votes

I'm currently updating all of our ETLs using Visual Studio 2015 (made in BIDS 2008) and redeploying them to a new reporting server running on SQL Server 2016 (originally 2008R2).

While updating one of the ETLs I got this error:

Exception from HRESULT: 0xC0202009 Error at Load Staging Table [OLE DB Source [129]]: 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 10.0" Hresult: 0x80004005 Description: "Invalid object name 'dbo.TimeSheets'.".

Here's what I've tried:

  • Checked my connection strings to make sure they were correct.
  • Checked the schema to make sure it existed and was correct.
  • Ran query from SSMS and it worked.
  • Ctrl + Shift + R to refresh intellisense.
  • Checked to see if another table exists with the same name.
  • Restarted Visual Studio and SSMS.
4
sqlsql-servertsqlssissql-server-2016
You're certain the connection string is pointing to the correct server and database? Is the SSIS package being run on a schedule using a different account that might have different permissions? – Siyual
I would check the default catalog for the connection string and ensure it is hitting the correct database (and server). The other option would be to fully qualify the table name {database}.{schema}.{table} to ensure that, regardless of the default catalog, you query the correct database. – Andrew O'Brien
Try running profiler and see what is actually being sent from SSIS to SQL Server. Maybe it's not hitting the database you think it is. – Tab Alleman
Were you able to figure this out? I'm having this issue where it's saying a temp table doesn't exist. It runs fine in SSMS... – april4181
A follow-up would indeed by most helpful as I'm having the exact same issue. – KSwift87

4 Answers

3
votes

I got a successful fix from the comments of the question "The other option would be to fully qualify the table name {database}.{schema}.{table} to ensure that, regardless of the default catalog, you query the correct database."

I was using some other database and it was caching and using that DB name rather than the new one I changed to.

1
votes

I encountered the same issue - my Database was somehow, not getting picked up. So I manually added the database and test the connection and now it works fine.

0
votes

For SSIS - Specially it is observed if any table name is coming as "Invalid object name" then Check as - First - Check table exist through SSMS and if not then SSMS> Edit > IntelliSense > Refresh Local Cache

Second - While making DB connection via "Connection manager" or already exist then - check table name comes in drop down of "Name of the table or View"

0
votes

I tried several of the above suggestions but what worked for me in the end was the good old close and re-open of SSDT!