March 6, 2017 at 8:34 am
hi
i have an ssis job that picks up .csv files and posts them into various tables.
when i manually run the job it works fine
when i schedule the job, it starts and finshes without error, but actually does nothing?
any ideas?
i have tried using a proxy account for the agent, which just gave me the same result
mal
March 7, 2017 at 5:15 am
ok some progress - sort of
i changed the folder location of my my files to full unc rather than a mapped drive
now i get a failure , but its simply
"executed as user "username" the step failed"
even when i use a full domain admin account?
any ideas?
March 7, 2017 at 7:11 am
Pretty good bet that the SQL Server Agent "service account" does not have permission to access the folder. When an Agent job runs, it runs under the auspices of the Agent Service's "service account", and that account is what needs to have permission to the relevant network share/folder.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 7:28 am
hi steve
i thought that to but
the agent is running on a network account, and i gave given the network account full access to the folder
i can log on as the network account, browse to the network path and delete / edit files fine
i also tried setting a proxy for the service account under a domain admin account, and it also failed!?
mal
March 7, 2017 at 8:40 am
Did you also check the SHARE permissions? NTFS volumes can have permissions at both the folder/file level as well as at the network share level. If you use the UNC path, the share permissions will be checked, and be aware that when the Agent job runs, it may not have access to a mapped drive, which is why you should always use UNC paths for this kind of thing. Also, can you post the exact errors you see in the log for the SSIS package?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 9:45 am
yes all shares are fine, i have given the user full read/write etc
i am only using unc
the error is nothing more than "executed as user "username" the step failed"
i have been playing around with the package and now its completing without error on the sql job - its just does nothing to the files :hehe:
March 7, 2017 at 10:57 am
Then the only possibility is that the account that the job runs under is perhaps not the one you think it is. A job CAN be configured to run under a context other than that of the Agent Service's service account. If that's the case, then find out what account it runs under. If you can't see which one, have a DBA run SQL Profiler to find out at the point in time that you run it. Either that, or I'd have to conclude that there's some data condition that the package detects and then just fails on purpose as it was designed to... and I mean by whomever wrote the package as opposed to Microsoft.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 11:09 am
sgmunson - Tuesday, March 7, 2017 10:57 AMThen the only possibility is that the account that the job runs under is perhaps not the one you think it is. A job CAN be configured to run under a context other than that of the Agent Service's service account. If that's the case, then find out what account it runs under. If you can't see which one, have a DBA run SQL Profiler to find out at the point in time that you run it. Either that, or I'd have to conclude that there's some data condition that the package detects and then just fails on purpose as it was designed to... and I mean by whomever wrote the package as opposed to Microsoft.
I think you're right - I believe the job only runs under Agent service account if the job owner is a sysadmin.
Sue
March 7, 2017 at 11:30 am
dopydb - Tuesday, March 7, 2017 7:28 AMhi steve
i thought that to but
the agent is running on a network account, and i gave given the network account full access to the folder
i can log on as the network account, browse to the network path and delete / edit files fine
i also tried setting a proxy for the service account under a domain admin account, and it also failed!?mal
Maybe something wasn't right with the proxy account. Try following the steps here - go to the section Creating a proxy for SSIS Package Execution:
Setting Up Your SQL Server Agent Correctly
Sue
March 8, 2017 at 9:41 am
ok, so to try and get more info i have tried running the package via cmdshell
now i get a bit more feedback! this runs fine in vstudio, but for some reason its trying to find the default file value assigned to the Variable currentfile, rather than take the value passed to it from the foreach loop - which should pass the fully qualified name??
Description: The system cannot find the file specified.
End Warning
Error: 2017-03-08 16:33:39.57
Code: 0xC020200E
Source: Data Flow Task Flat File Source [1]
Description: Cannot open the datafile "abc.csv".
End Error
Error: 2017-03-08 16:33:39.57
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Progress: 2017-03-08 16:33:39.57
Source: Data Flow Task
Pre-Execute: 33% complete
End Progress
Progress: 2017-03-08 16:33:39.57
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2017-03-08 16:33:39.57
Source: Data Flow Task
Cleanup: 33% complete
End Progress
Progress: 2017-03-08 16:33:39.57
Source: Data Flow Task
Cleanup: 66% complete
End Progress
Progress: 2017-03-08 16:33:39.57
Source: Data Flow Task
Cleanup: 100% complete
End Progress
Warning: 2017-03-08 16:33:39.57
Code: 0x80019002
Source: Package
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specifie
d in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 16:33:39
Finished: 16:33:39
Elapsed: 0.249 seconds
March 8, 2017 at 9:47 am
plus as you guys already know 🙂
it is definitly something to do with reading from the network, as locally it works fine!!
i did think using a sql script with cmdshell command to run the package would of used my credentials though? so its strange it fails?
sort of baffled now !!
March 8, 2017 at 1:32 pm
dopydb - Wednesday, March 8, 2017 9:47 AMplus as you guys already know 🙂it is definitly something to do with reading from the network, as locally it works fine!!
i did think using a sql script with cmdshell command to run the package would of used my credentials though? so its strange it fails?
sort of baffled now !!
Since you got it to fail using xp_cmdshell, maybe try to server, login and do a xp_cmdshell 'dir <your folder for the files>'
to see if your able to see that folder under the same circumstances. If you are then it could still be something with the variable mapping for the files.
And since your head is probably spinning from trying to figure it out, if you can see that folder, try walking through this setup on the file name variables:
Introducing the Foreach Loop Container
Sometimes I can stare at something for so long that I see the correct settings even when they aren't correct.
Sue
March 9, 2017 at 6:33 am
ok
small progress again, firewall was blocking some ports on remote computer so i think i have passed the network access issue
now my forloop does not seem to be setting the filename variable correctly
i have a
foreach file enumerator,
a full unc path of \\grnfert01\d\Logs\KPIV\Mixer\test
files *.csv*
retrieve full qualified
with a mapped variable for it to write to - default value abc.csv
but when i run from a script i get below error - basically it only seems to look for abc.csv...
Code: 0x80070002
Source: Data Flow Task csv location [1]
Description: The system cannot find the file specified.
End Warning
Error: 2017-03-09 12:37:40.11
Code: 0xC020200E
Source: Data Flow Task csv location [1]
Description: Cannot open the datafile "abc.csv".
End Error
March 9, 2017 at 8:44 am
dopydb - Thursday, March 9, 2017 6:33 AMoksmall progress again, firewall was blocking some ports on remote computer so i think i have passed the network access issue
now my forloop does not seem to be setting the filename variable correctly
i have a
foreach file enumerator,
a full unc path of \\grnfert01\d\Logs\KPIV\Mixer\test
files *.csv*
retrieve full qualified
with a mapped variable for it to write to - default value abc.csvbut when i run from a script i get below error - basically it only seems to look for abc.csv...
Code: 0x80070002
Source: Data Flow Task csv location [1]
Description: The system cannot find the file specified.
End Warning
Error: 2017-03-09 12:37:40.11
Code: 0xC020200E
Source: Data Flow Task csv location [1]
Description: Cannot open the datafile "abc.csv".
End Error
Possibly a dumb question, bud does abc.csv exist? And are you giving the full path of abc.csv?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 9, 2017 at 9:46 am
the abc.csv is just a placeholder for variable the forloop should pass to it
if i take out the abc, i get the below error - yes i have set the for loop to pass the full qualified name to the variable for the file location (works fine when in vstudio)
Warning: 2017-03-09 16:43:25.76
Code: 0x80070003
Source: Data Flow Task csv location [1]
Description: The system cannot find the path specified.
End Warning
Error: 2017-03-09 16:43:25.76
Code: 0xC020200E
Source: Data Flow Task csv location [1]
Description: Cannot open the datafile "".
End Error
Error: 2017-03-09 16:43:25.76
Code: 0xC004701A
Source: Data Flow Task SSIS.Pipeline
Description: component "csv location" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply