SSIS Package Permission Proxy Account
-
I have an issue that I can't get around, I had a working solution but something got changed somewhere and now its broken.
I have an ssis file package that that uses an AD account to connect to another server and move data.
Executed as user: AD|UN. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 6:00:01 AM Error: 2017-08-29 06:00:10.12 Code: 0xC0047070 Source: Data Flow Task 1 Description: End Error Error: 2017-08-29 06:00:10.13 Code: 0x80004005 Source: Data Flow Task 1 Description: End Error Error: 2017-08-29 06:00:10.13 Code: 0xC0208266 Source: Data Flow Task 1 Description: End Error Error: 2017-08-29 06:00:10.18 Code: 0xC0208265 Source: Data Flow Task 1 Source - hp_Change_Main [393] Description: Failed to retrieve long data for column "Impacted_Countries". End Error Error: 2017-08-29 06:00:10.20 Code: 0xC020901C Source: Data Flow Task 1 Source - hp_Change_Main [393] Description: There was an error with Source - hp_Change_Main.Outputs[OLE DB Source Output] on Source - hp_Change_Main. The column status returned was: "DBSTATUS_UNAVAILABLE". End Error Error: 2017-08-29 06:00:10.20 Code: 0xC0209029 Source: Data Flow Task 1 Source - hp_Change_Main [393] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Source - hp_Change_Main.Outputs[OLE DB Source Output]" failed because error code 0xC0209071 occurred, and the error row disposition on "Source - hp_Change_Main" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. End Error Error: 2017-08-29 06:00:10.21 Code: 0xC0047038 Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - hp_Change_Main returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: 2017-08-29 06:00:10.22 Code: 0xC02020C4 Source: Data Flow Task 1 Source 1 - hp_Change_Task [660] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2017-08-29 06:00:10.23 Code: 0xC0047038 Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source 1 - hp_Change_Task returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: 2017-08-29 06:00:10.23 Code: 0xC02020C4 Source: Data Flow Task 1 Source 2 - hp_Change_Task_SLA [741] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2017-08-29 06:00:10.24 Code: 0xC0047038 Source: Data Flow Task 1 SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source 2 - hp_Change_Task_SLA returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:00:01 AM Finished: 6:00:10 AM Elapsed: 9.25 seconds. The package execution failed. The step failed.
I was getting another message that it didn't have permission to execute the package, I got past that with a group security policy change but this one has me beat. I can log into the server and execute the package as the proxy account without a hitch, no I feel like I am chasing my tail.
-
So after much playing with many permissions DTS_E_CANTCREATESPOOLFILE
It looks like my issue is that there is an issue with the agent account and the proxy account and they don't have permission to the directory where the pool file is trying to be written but at this point I think I am just guessing HELLLPPPP
- johnhancocktx - Tuesday, August 29, 2017 10:33 AM
It's a spoolfile, not a pool file, by the way
This is a very unusual error!
If you select the data flow component on the control flow and check the 'BufferTempStoragePath' property, does it have a value?
If not, my guess would be that whatever the default temp storage folder is is causing the problem. I do not know what the default is, unless it's whatever you see as TEMP's value when running SET from a command prompt. -
I know it was spool and not pool, thanks I've been up a while working on this and moestiems nifgers tond korw.
-
Well I have at least fixed my problem but I am not sure what I did and now I need to roll backwards to figure it out.
You can not create a proxy account if the users is sysadmin, but apparently you can make the account a sys admin after you create the proxy. The file group was out of room so I am not sure if the issue was the proxy account did not have permission to grow the data base or if the issue was something else but at least for now its resolved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply