February 7, 2020 at 10:12 am
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?
February 7, 2020 at 10:28 am
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
February 7, 2020 at 10:45 am
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
February 7, 2020 at 11:30 am
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
February 7, 2020 at 11:51 am
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
February 7, 2020 at 11:51 am
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?
February 7, 2020 at 12:00 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply