How to fix - Cannot find object/table in database error when it actually exists ?

• ShinyBaldHead

  SSC-Addicted

  Points: 424

  More actions

  January 30, 2014 at 5:18 pm

  #281809

  I made a console C# app in which I generate SQL to insert a row into a table info_table and then execute it. When I use the same C# code (with necessary modifications of course) in SSIS, I get an error saying that the table cannot be found.

  Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.

  ---> System.Data.SqlClient.SqlException: Invalid object name 'info_table'.

  I know there is a info_table in my database and it works in the console app. So, I tried to use the fully qualified name in my SSIS C# script instead of just info_table and the error went away. Why does this happen and how do I fix it ? I doubt if there is a problem with my SSIS connection string.

  My SSIS connection String (connection manager is ADO.NET type) -

  Data Source=.;User ID=admin;Initial Catalog=MaximDB;Persist Security Info=True;Application Name=SSIS-DataReader-{555000dddd-aaa-bbb-cccceee}LocalHost.MaximDB.admin;

  My console app connection String -

  "server=(local);database=MaximDB;integrated security=SSPI;";

  The C# objects to connect to the database -

  For Console C# app -

  Console connection object - new SqlConnection(consoleString);

  For SSIS C# script -

  String ssisDbConnMgr = "SAMPLE_DB";//Name of the connection manager in SSIS, an ADO.NET type

  ConnectionManager connMgr;

  SqlConnection databaseConn;

  SqlCommand sqlCmd;

  connMgr = Dts.Connections[ssisDbConnMgr];

  databaseConn = (SqlConnection)connMgr.AcquireConnection(Dts.Transaction);

  sqlCmd = new SqlCommand();

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 31, 2014 at 12:20 am

  #1684938

  If the table is not in the user's default schema nor is it in dbo, you'd need to qualify the table name with the schema name to access it.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• ShinyBaldHead

  SSC-Addicted

  Points: 424

  More actions

  January 31, 2014 at 11:06 am

  #1685153

  GilaMonster (1/31/2014)

  ---

  If the table is not in the user's default schema nor is it in dbo, you'd need to qualify the table name with the schema name to access it.

  I am not able to understand this. Why should there be a problem ? Even Execute SQL tasks use the exact same connection and SQL (NOT fully qualified name) and are able to find the tables.

• ShinyBaldHead

  SSC-Addicted

  Points: 424

  More actions

  January 31, 2014 at 11:54 am

  #1685171

  I tried 'USE MaximDB' before my SQL statements and the SSIS package worked. I wonder why.

• Eric M Russell

  SSC Guru

  Points: 125595

  More actions

  January 31, 2014 at 12:57 pm

  #1685183

  ShinyBaldHead (1/31/2014)

  ---

  I tried 'USE MaximDB' before my SQL statements and the SSIS package worked. I wonder why.

  Perhaps are your connection settings being overridden by config file?

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• ShinyBaldHead

  SSC-Addicted

  Points: 424

  More actions

  January 31, 2014 at 1:03 pm

  #1685185

  Eric M Russell (1/31/2014)

  ---

  ShinyBaldHead (1/31/2014)

  ---

  I tried 'USE MaximDB' before my SQL statements and the SSIS package worked. I wonder why.

  Perhaps are your connection settings being overridden by config file?

  There is no config file. I checked the package configurations.