Why do Execute SQL Tasks create table names with an appended Hexadecimal value?

Question

Why do Execute SQL Tasks create table names with an appended Hexadecimal value?

Weegee2017

SSC Eights!

Points: 994
More actions
February 7, 2020 at 10:12 am

#3723854

I am updating an SSIS package which has an Execute SQL task.
This task creates a temp staging table for loading table with the following syntax:
create table dbo.PushPortfolioData
(
PartyURN varchar(255),
FirstName varchar(255),
LastName varchar(255),
EmailAddress varchar(255),
AddressLine1 varchar(255),
AddressLine2 varchar(255),
AddressLine3 varchar(255),
AddressLine4 varchar(255),
AddressLine5 varchar(255),
PostCode varchar(255),
PayrollRef varchar(255),
NINumber varchar(255),
NotificationMethod varchar(255),
Corporate varchar(255)
)
The package then proceeds to bulk insert data in.
I then want to query the rowcount of the table with the following statement:
Select COUNT(*) From dbo.PushPortfolioData
This statement fails with the following error message
[Execute SQL Task] Error:
Executing the query "Select COUNT(*) From dbo.PushPortfolioData" failed with the following error:
"Invalid object name 'dbo.PushPortfolioData'.".
Now upon looking at Sysobjects I see that the table dbo.PushPortfolioData_A2488A8ACD43465D9A357D33A3478440
has been created.
My question is why has the table been created with '_A2488A8ACD43465D9A357D33A3478440 ' appended to the table name
of PushPortfolioData?
When I expected to see the table name dbo.PushPortfolioData?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

why has the table been created with '_A2488A8ACD43465D9A357D33A3478440 ' appended to the table name

It wouldn't be; not natively anyway. To me, this suggests you have some DDL triggers, that's changing the name add a GUID (without the hyphens (-)) as a suffix, or the SQL you're running isn't what you think it is.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

There was a trigger which was unconnected with this part of the database.

I disabled that trigger and re-ran my code

I still get table created with the GUID

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

What else is happening in the DTSX? Can you post a screen grab of the control flow and data flow?

Something is injecting the additional value somewhere in the process

Weegee2017 SSC Eights! Points: 994 More actions · Answer 4

Hi anthony

Attached is a screen grab of the whole flow and a screen shot of the task that creates the table

Attachments:

You must be logged in to view attached files.

Thom A SSC Guru Points: 99130 More actions · Answer 5

Weegee2017 wrote:

Attached is a screen grab of the whole flow and a screen shot of the task that creates the table

That doesn't show us a lot; we need to see the "inner" workings. Also, all of those tasks have expressions on them that's making something on them dynamic.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

So you got a number of parameterised execute SQL tasks there, one is "Generate GUID", my guess is that GUID is being passed down the chain, so its the SSIS package which is adding the additional value.

Have you debugged it all and stepped through this piece by piece?

Weegee2017 SSC Eights! Points: 994 More actions · Answer 7

Hi Anthony

I just realized that on running a trace. I thought the guid was being used elsewhere but it is being generated and used further down.

Sorry I hadn't spotted this and just thought the native sql above was being executed.

But I can now reference this precise variable by grabbing the table name with the guid

Thanks for your help Anthony in spotting this