January 4, 2013 at 6:38 am
Hi, so I'm very new to SQL Server but I keep running into this error when I manually run a job from SQL Server Agent.
The error from the log files:
"Source: Truncate RAW Table Execute SQL Task Description: Executing the query "TRUNCATE TABLE [EdwStaging].[RAW].[DimUnitOfMeasur..." failed with the following error: "Cannot find the object "DimUnitOfMeasure" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
I've tried running SQL Server Management Studio as an Admin and it still returns the error. I'm able to run the individual package that it's failing on successfully but when I run the bigger master package, it fails.
Any ideas would be very appreciated!
January 4, 2013 at 6:43 am
hogpen (1/4/2013)
Hi, so I'm very new to SQL Server but I keep running into this error when I manually run a job from SQL Server Agent.The error from the log files:
"Source: Truncate RAW Table Execute SQL Task Description: Executing the query "TRUNCATE TABLE [EdwStaging].[RAW].[DimUnitOfMeasur..." failed with the following error: "Cannot find the object "DimUnitOfMeasure" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
I've tried running SQL Server Management Studio as an Admin and it still returns the error. I'm able to run the individual package that it's failing on successfully but when I run the bigger master package, it fails.
Any ideas would be very appreciated!
If you are running the TRUNCATE statement from a job step, make sure the step is referencing the correct database. Sounds like the step is trying to run in some database other than EdwStaging.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
January 4, 2013 at 8:56 am
I used:
GRANT ALTER ON [EdwStaging].[RAW].[DimUnitOfMeasure...] TO [SSISExecuter]
GO
I was told by a coworker to use that because the job runs through a service account that has table specific access, so for each new table I have to grant permissions to that role.
I re-ran the job and it failed again. The above error no longer exists, but all it says is:
"Started: 9:33:16 AM Finished: 9:47:17 AM Elapsed: 841.142 seconds. The package execution failed. The step failed.,00:14:02,0,0,,,,0"
I can't find any explanation for the failure. Any ideas?
January 4, 2013 at 9:16 am
hogpen (1/4/2013)
I used:GRANT ALTER ON [EdwStaging].[RAW].[DimUnitOfMeasure...] TO [SSISExecuter]
GO
I was told by a coworker to use that because the job runs through a service account that has table specific access, so for each new table I have to grant permissions to that role.
I re-ran the job and it failed again. The above error no longer exists, but all it says is:
"Started: 9:33:16 AM Finished: 9:47:17 AM Elapsed: 841.142 seconds. The package execution failed. The step failed.,00:14:02,0,0,,,,0"
I can't find any explanation for the failure. Any ideas?
Don't think ALTER is the permission you want, as I think that applies to changes in the table's structure (I'll be corrected if I'm wrong, so stay tuned on that:-D). Perhaps GRANT DELETE would be better?
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
January 4, 2013 at 9:27 am
After I ran the ALTER and then the job, it no longer came up with the error:
"Source: Truncate RAW Table Execute SQL Task Description: Executing the query "TRUNCATE TABLE [EdwStaging].[RAW].[DimUnitOfMeasur..." failed with the following error: "Cannot find the object "DimUnitOfMeasure..." because it does not exist or you do not have permissions."
So it did something. The job ran for an additional 180 seconds after I ran the ALTER query so I think it did something right.
I'm very confused and frustrated.
January 4, 2013 at 9:29 am
Roland Alexander STL (1/4/2013)
Don't think ALTER is the permission you want, as I think that applies to changes in the table's structure (I'll be corrected if I'm wrong, so stay tuned on that:-D). Perhaps GRANT DELETE would be better?
Roland, TRUNCATE is a DDL operation (as opposed to DML), so ALTER is probably the right permission.
Original poster, have you tried opening the package with the Package Execution Utility and running it through that to see what the error causing the failure is? (Beware that since it'll be running as you rather than as the SQL Agent account, it may behave differently.)
John
January 4, 2013 at 9:33 am
Truncate requires elevated permissions beyond GRANT DELETE. I think you need to be db_owner.
If the table is small you can use a DELETE without a where clause.
Or explore ways of getting around the permissions issue.
http://voiceofthedba.wordpress.com/2011/03/07/sql-server-truncate-table-permissions/
January 4, 2013 at 9:56 am
I just ran the package again, this time not as an admin, and received the following error:
"Source: Extract_DimUnitOfMeasure_TIPS... Description: Error 0x80070002 while preparing to load the package. The system cannot find the file specified. . End Error DTExec: The package execution returned DTSER_FAILURE (1)."
Which doesn't make any sense to me. I have imported the Extract_Dim... package from my computer to the stored packages folder and can see the package next to all the other packages.
John, I've tried to run the package using Execute Package Utility and received a network error, maybe a firewall issue? My coworker just said to try and run it from SQL Agent.
Still very lost. :unsure:
January 4, 2013 at 10:40 am
I have an individual Master_DimUnitOfMeasure package that is called in the larger master package in the job that keeps failing on me.
When I run the Master_DimUnitOfMeasure from my computer in SSIS, everything completes successfully.
January 4, 2013 at 11:04 am
SSIS package does not run when called from a SQL Server Agent job stephttp://support.microsoft.com/kb/918760
Its a fairly common issue and there are tons of resources if you google 'package fails SQL server agent'
If I had to guess Iwould say the account the agent is running under does not have truncate permissions.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply