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

Question

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.

Ashwin

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? – Phytogeography

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. – Triturate

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. – Procrustes

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... – Mcmillen

A follow-up would indeed by most helpful as I'm having the exact same issue. – Synsepalous

Answer 1

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.

Poetry

Had similar issue, was somehow baffled that my query runs in SSMS but not SSIS... only when i saw this answer hit me why i keep getting the error... – Pulver

Answer 2

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.

Cache

Answer 3

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"

Vani

Answer 4

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!

Scripture

Answer 5

I just had the same issue.

Both SSIS and SSMS refused to find the table [Stage].[Customer], although it definetly existed (and had existed for quite some time).

Dropping and re-creating the table did no good. Also I noticed that SSMS's intellisense did not pick up that the table had been re-created (yes, after ctrl+shift+R)

What helped in the end was using

CREATE TABLE [Stage].[Customer] ([Id] INT)

in SSMS

This caused an error because the table already existed. Since then both SSMS and SSIS are working as expected. No idea what caused this error, but it is been difficult to track down.

Waki

Answer 6

Same issue.

I could see the corresponding tables in the drop-down in several of my OLE DB Destinations, but it would not let me see the mappings, giving the following error: "Exception from HRESULT: 0xC0202040... SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005."

What did not work:

• Restarted Visual Studio
• Several solution cleans/rebuilds
• Restarted the computer

What did work:

• Closed Visual Studio
• Cleared files in %temp% folder

Details When clearing my %temp% files (those that would delete), one folder (VsHub) would not delete, but I was able to delete some of the files that it contained. When I started Visual Studio again and opened the sequence that contained those OLE DB Destinations, it was obvious that it was re-evaluating the XML code and the error went away.

Hope this helps someone.

Lenin