April 30, 2015 at 8:55 am
Hello,
I recently installed standalone version of SQL 2014 Standard on my work computer. I used Access before but I want to use a SQL server instead.
Now here is my problem.
We have a shared drive that a file gets deposited every day at midnight. I want to be able to get this file and import it to the server (its basically a list of names).
Here what I have done so far:
I created the database
Created the file and successfully imported data into it using the Import Data feature.
I saved the SSIS package
Scheduled an Agent Job for this package to run at certain time,daily
At first the jobs would fail with a Access is Denied. I added a user under Credentials with my network account ( have admin rights on the work computer).
Also added a Proxy for the Credential user I made.
Jobs fail with a “Cannot open data file” error. I tried changing things here and there, but I can’t get it to work.
I am sort of a noob to SQL 2014.
Thanks!
April 30, 2015 at 9:04 am
Is there a chance that some other process has the file open when the job runs? Are you editing it yourself? 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 30, 2015 at 9:11 am
Hi Phil,
Once the file is deposited in the shared drive, there is no other process that uses that file.
I read other articles about permissions, but I think that’s sort of beyond my current knowledge. I have read/write access to this shared file, so idk.
Also, when I run the package manually, it runs fine.
April 30, 2015 at 9:15 am
xddevv (4/30/2015)
Hi Phil,Once the file is deposited in the shared drive, there is no other process that uses that file.
I read other articles about permissions, but I think that’s sort of beyond my current knowledge. I have read/write access to this shared file, so idk.
Also, when I run the package manually, it runs fine.
When a package works for a user but not as a job, it's 99% likely to be a permissions issue.
Your SQL Agent user needs rights to access the file.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 30, 2015 at 9:19 am
How can I add the necessary rights? Noob question 😛
April 30, 2015 at 11:25 am
Well if this is just an install on your workstation no one else is using you could change the SQL Server agent to use your account and see what happens <.<
Oh and are you using a network drive or a full server path to the share to connect?
April 30, 2015 at 12:07 pm
If your file is on a network drive, the SQL Agent user needs permission to that network location. If you tell your network administrator the user you need, they'll be able to assign the necessary permissions for you.
April 30, 2015 at 2:03 pm
I was hoping it wouldnt involve Admins, but i guess so. They just take forever here (public agency).
Thanks for the help yall!
May 1, 2015 at 11:11 am
hey guys so I think I figured it out.
My last question, when I ask for permissions to the specific folder, do persmissions also have to apply to the whole drive as well?
for example:
The file I want is here on the network drive:
s:\allusers\importantfiles\file.txt
When I request permissions, do I tell them to give me permissions to the importantfiles folder only or to the whole s network drive so the Agent scheduler can see it?
May 1, 2015 at 11:16 am
xddevv (5/1/2015)
hey guys so I think I figured it out.My last question, when I ask for permissions to the specific folder, do persmissions also have to apply to the whole drive as well?
for example:
The file I want is here on the network drive:
s:\allusers\importantfiles\file.txt
When I request permissions, do I tell them to give me permissions to the importantfiles folder only or to the whole s network drive so the Agent scheduler can see it?
You should not require access to the whole drive, just to the folder(s) that you will be accessing.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 1, 2015 at 11:30 am
xddevv (5/1/2015)
hey guys so I think I figured it out.My last question, when I ask for permissions to the specific folder, do persmissions also have to apply to the whole drive as well?
for example:
The file I want is here on the network drive:
s:\allusers\importantfiles\file.txt
When I request permissions, do I tell them to give me permissions to the importantfiles folder only or to the whole s network drive so the Agent scheduler can see it?
Your SQL Agent likely won't be able to use the S:, it'll need the direct path name something like \\<shares servername>\allusers\importantfiles\file.txt
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply