0

I have a pipeline that loads multiple csv's and an xlsx file into 4 separate tables. Generally, this pipeline runs fine. However, occasionally one of the tables (always the same table) results in an invalid object. If I re-run the pipeline with no changes, all is fine. I have never had an issue with any of the other tables loaded by the pipeline. The table fields are all nvarchar(max) and the data is relatively simple. It is also a very small table. Well under 100 rows, and the data in the source rarely changes. It is definitely not changing between successful and unsuccessful runs.

I am interested in any ideas on what may be causing this periodic failure. All runs fine more than 95% of the time. When it fails, it kills the rest of the load process.

John Gretton
  • 1

2 Answers2

0

"separate tables" ? The error message points to the fact the table into which you are trying to insert the data does not exist and hence the error .

Are you dropping and creating the tables everytime the pipeline runs ? If yes is the drop table pre script in each of the copy activity or only one ? I am assuming thats only in one copy activity . If thats the case the activity with the prescript should be the first one and then you can add the all the three after that . I am making assumption , if this feedback was not helpfull , share more info on how the pipeline are authored and that will help .

HimanshuSinha
  • 1,650
  • 2
  • 6
  • 10
  • The question is really why does the table occasionally not exist after the pipeline runs.. The pipeline as a 'Pre-copy script' that checks for the existence of the table and drops it. The intention is to create a new version of the table each time the pipeline runs. The other three tables that are loaded by the pipeline work exactly the same way. each has its own identical Pre-copy script to check for and delete the existing table. To date, I have only had an issue with the one table. – John Gretton Nov 17 '22 at 22:31
  • Since the error says that "invalid object name" and you have auto table create option set thats means that some other process is droping the table ( while copy is in flight ) . I am not sure what process is droping the table , but i could have started from the pre-copy .post copy scripts ( if any ) – HimanshuSinha Nov 18 '22 at 00:45
0

You could refer to this doc about error 208 for the root cause and solution.

This error can be caused by one of the following problems:

1.The object is not specified correctly.

2.The object does not exist in the current database or in the specified database.

3.The object exists, but could not be exposed to the user. For example, the user might not have permissions on the object or the object is created within an EXECUTE statement but accessed outside the scope of the EXECUTE statement.

Ceeno Qi-MSFT
  • 924
  • 1
  • 3
  • 5