February 21, 2020 at 9:27 am
Hi SSC,
I'm having issues with one SSIS Package running on SQL Server 2017.
What it does is basically:
When I run the Source Statement wether it's as a source preview or SQL Statement it works as expected.
When I run the package from VS2019 it also does insert all rows. When I run the package directly from the SSIS Catalog, it works.
When I schedule a job it doesn't. When I create an insert statement it works from SSMS without any warnings or anything, when I take that statement and use it in a execute SQL Task it inserts 0 rows again.
Any ideas where to look any further? I'm literally out of clues.
SSIS Package and DB reside on the same server and up to that table everything else works as intended.
February 21, 2020 at 9:30 am
Sounds like the SQL Server Agent doesn't have permissions to do what ever you're asking it to do. For all those scenarios, the package would be using the same credentials (yours) apart from when it's called from an Agent Job. I would start there.Have you checked the logs for the package as well? The logs with the SSIS Catalog for package execution are very verbose.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 21, 2020 at 9:37 am
Permissions is the obvious thing. Assuming your job doesn't use a proxy, it'll run in the context of the SQL Server Agent service account. If you use an EXECUTE AS statement in SSMS to run the insert in the same context, what happens? What I've seen before is that the SQL Server Agent account is created as a service principal, and so when SQL Server Agent connects it gets the more restrictive permission set of that and the built-in NT SERVICE\SQLSERVERAGENT account. If the explicitly created principal isn't as privileged as the built-in one, that can lead to unexpected behaviour.
John
February 21, 2020 at 10:02 am
ok, I understand permissions is the obvious thing so let me dive a bit into that direction:
Yes the SQL Service Accounts do have registered SPNs (MSSQLSvc/ is registered to the DB Engine Account) and uses AD Accounts for all services (DB Engine, SQL Agent). We use Windows Authentication but I still somehow have to doubt that it's a permission thing. First of all it's the very last table in the whole package to be touched, we're talking about 20 - 30 tables before this one, part of them within the same schema. Secondly there is no proxy, I'm using the SQL Server Agent to run the package, job owner is sa.
I'm trying to run the statement as both sa and the SQL Service Agent AD Account, will come back with results.
February 21, 2020 at 10:25 am
You didn't answer the question about whether the SQL Server Agent account is explicitly created as a login
-- This may not work if the SQL Server Agent account is shown
-- in dm_server_services with "@" instead of "\" notation
SELECT l.name FROM sys.server_principals l
JOIN sys.dm_server_services s ON l.name = s.service_account
WHERE s.servicename LIKE 'SQL Server Agent%';
Also, you can answer the permissions question once and for all by creating a table for the output of this query and inserting the output into the table in a job step.
SELECT
entity_name
, subentity_name
, permission_name
FROM sys.fn_my_permissions('MySchema.TroublesomeTable','OBJECT');
John
February 21, 2020 at 11:50 am
No, the SQL Agent isn't explicitly created as a login, if it is in fact an issue because there is no login for the SQL Agent Account, then I have directly attached my next question again: Why does it only happen on this table? All previously changed tables within this package are in the same DB and the same schema. Yes EXECUTE AS LOGIN='SQLAGENTLOGIN' brings up no permissions on the DB, 'sa' has no issues.
February 21, 2020 at 3:24 pm
What is the source for this table?
What are the sources for the other tables?
Is there anything 'different' about either the source or the target for the problematic table?
("Different" = Different source folder, different source file type, different target table schema etc etc)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 21, 2020 at 4:10 pm
Well to be exact the package is failing on the last fact table I'm trying to fill, it's data is mostly based on the previously finished fact table.
Well the source for the other tables begins with either external DB or Flatfile, goes through transformation into dimension tables and from there on to the facts.
In this case for the previous table the source are dimension tables within the same DB, and both (Dimension and Fact tables) are within the same dbo schema, we only use other schemas for extract and transform. There is about 15 dimension tables being built before any of the Fact Tables are being built.
Meanwhile I can confirm that adding the SQL Agent Service account (to sysadmin) does make the message about permissions disappear when running the statement with EXECUTE AS LOGIN= but the last attempt via execute SQL Task didn't yield any inserted rows unfortunately.
The latter to me makes sense because otherwise there would be no reason why the other tables are treated differently (rows are inserted correctly). Even if the SQL Agent Account would come as NT SERVICE\SQLSERVERAGENT, this account has per default sysadmin permissions.
February 21, 2020 at 4:43 pm
Does the final SELECT take the same length of time, regardless of whether the rows are inserted or not? Just wondering whether the query is actually executing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 24, 2020 at 7:36 am
It runs for about 12 seconds before it reports successful. Which is about the same amount of time until first rows are displayed. The query runs for a total of 2:30 minutes.
February 27, 2020 at 8:21 am
There was a part of the query as a INNER JOIN clause which had trouble with lookups due to the package not being adopted to In-Memory Tables (TRUNCATE TABLE was in use essentially) which somehow made the lookup outputs drift away. After fixing that issue both query and INSERT are working as expected.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply