February 13, 2008 at 3:17 pm
Hello,
The error I'm getting is that it can't create or access the "filename".csv file on the local drive. I have all the SQL Services running under the doman admin acct, but for some reason the job will just not run successfully. I also have tried to set the permissions for the job to every power user possible (e.g. "sa", "domain admin", "NT Auth\..").
Please help or I'm ready to toss this server out the window.
Thanks!!! 😀
-Dave
February 14, 2008 at 11:02 am
How do you have "filename".csv listed in your connection manager? Don't forget that when you run it as yourself all of your mappings are there and the package is looking at your local drive. Assuming you aren't running SSDS directly off of the server through RDP or something similar.
If your CSV is on your local hard drive, or even a mapped network drive the SQL Agent won't be able to see it. Make sure you put the UNC address or that the file is local to the server itself.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 14, 2008 at 11:52 am
RDP as the domain acct to the machine on which the SSIS package is running.
Can you access the drive, using the same path as the one specified in the package?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 14, 2008 at 12:09 pm
I'm RDP'd to the server and consoled in. I'm running this package on the actual server and I have access to the local drives. I'm also logged in as the domain admin and the package is setup in SQL Agent as domain admin. Please help guys!!! I'm stumped! 🙂
February 14, 2008 at 12:22 pm
This is kind of grasping at straws but is there any chance you are using a package configuration, or a variable for the name of the file? Basically anything that might change the path during the execution of the package?
If not have you thought about trying a simple test of moving the file to c:\temp\file.txt and see if you can find it there.
Also if its a network drive make sure you use the UNC path for it rather than a drive letter. Its clearer and you are less likly to have issues.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 15, 2008 at 3:12 am
davidsalazar01 (2/13/2008)
Hello,The error I'm getting is that it can't create or access the "filename".csv file on the local drive. I have all the SQL Services running under the doman admin acct, but for some reason the job will just not run successfully. I also have tried to set the permissions for the job to every power user possible (e.g. "sa", "domain admin", "NT Auth\..").
Please help or I'm ready to toss this server out the window.
Thanks!!! 😀
-Dave
While logged in as the domain acct, can you create/delete a new file in that drive location?
Try creating a new file test.txt. Can you do it?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 15, 2008 at 7:34 am
Yes, I can create/delete a file while logged in as domain admin.
Any other suggestions??? 🙂
February 15, 2008 at 7:55 am
(1)
Is it possible for you to run the package in debug mode using Visual Studio locally on that server?
Would it run successfully?
I assume that when you were developing and testing the package in Visual Studio, prior to deployment, it was running fine.
Were you using the same filepath then?
(2)
Grab the command used by the job to run the package and run it manually on the server itself, while logged in locally as the admin account.
(3)
In SSMS 2005, connect to your Integration Services instance. Navigate to your package and select Execute. Does it run successfully?
These are the things I would try in your situation - I also don't have a clue why you are having this issue at the moment.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 15, 2008 at 8:10 am
Marios Philippopoulos (2/15/2008)
(1)Is it possible for you to run the package in debug mode using Visual Studio locally on that server? Yes, it runs fine when I run each component seperately.
Would it run successfully? Yes.
I assume that when you were developing and testing the package in Visual Studio, prior to deployment, it was running fine.
Were you using the same filepath then? Same file path.
(2)
Grab the command used by the job to run the package and run it manually on the server itself, while logged in locally as the admin account. This I haven't tried yet, but I'll try it.
(3)
In SSMS 2005, connect to your Integration Services instance. Navigate to your package and select Execute. Does it run successfully?
I'm getting a DLL error when I connect to Integration Services instance from SSMS. This is another seperate issue I'm troubleshooting also.
These are the things I would try in your situation - I also don't have a clue why you are having this issue at the moment.
February 15, 2008 at 8:12 am
davidsalazar01 (2/15/2008)
Marios Philippopoulos (2/15/2008)
(1)Is it possible for you to run the package in debug mode using Visual Studio locally on that server?
Yes, it runs fine when I run each component seperately.
Would it run successfully? Yes.
I assume that when you were developing and testing the package in Visual Studio, prior to deployment, it was running fine.
Yes, it was running fine on the previous Prod box.
Were you using the same filepath then?
Same file path.
(2)
Grab the command used by the job to run the package and run it manually on the server itself, while logged in locally as the admin account.
This I haven't tried yet, but I'll try it.
(3)
In SSMS 2005, connect to your Integration Services instance. Navigate to your package and select Execute. Does it run successfully?
I'm getting a DLL error when I connect to Integration Services instance from SSMS. This is another seperate issue I'm troubleshooting also.
These are the things I would try in your situation - I also don't have a clue why you are having this issue at the moment.
February 16, 2008 at 12:21 am
You wrote:
"I'm getting a DLL error when I connect to Integration Services instance from SSMS. This is another seperate issue I'm troubleshooting also."
What is the error?
"Cannot find SSPI context"?
If this is it, I would suggest rebooting the SSIS machine. A while ago, I had to troubleshoot this issue with Microsoft on the phone, and the solution in the end was to reboot.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 16, 2008 at 12:28 am
Is it possible for you to run the package in debug mode using Visual Studio locally on that server?
Yes, it runs fine when I run each component seperately.
Would it run successfully? Yes.
I assume that when you were developing and testing the package in Visual Studio, prior to deployment, it was running fine.
Yes, it was running fine on the previous Prod box.
Were you using the same filepath then?
Same file path.
Navigate to Program Files --> ... --> Business Intelligence Development Studio (BIDS) and right-click on BIDS. Select "Run As" and connect using the SQL Agent account. Run your package in debug mode. Do you get the same error?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 16, 2008 at 3:31 am
Cannot find SSPI context is probably related to the SPN(service principal name) for the SQL Server not being set correctly.
You can check this using adsiedit but intsead of explaining it badly myself here's a few links about this
http://poseidom.wordpress.com/2007/12/16/set-spn-for-sql-2005-sccm-remote-sql-fix/
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
http://support.microsoft.com/kb/319723
Anyway if use a normal domain account with very low privileges as recommended it is very common for the above to be incorrectly configured as BOL tells you nothing about any of this.
hth
David
February 16, 2008 at 4:00 am
David Scotland (2/16/2008)
Cannot find SSPI context is probably related to the SPN(service principal name) for the SQL Server not being set correctly.You can check this using adsiedit but intsead of explaining it badly myself here's a few links about this
http://poseidom.wordpress.com/2007/12/16/set-spn-for-sql-2005-sccm-remote-sql-fix/
http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
http://support.microsoft.com/kb/319723
Anyway if use a normal domain account with very low privileges as recommended it is very common for the above to be incorrectly configured as BOL tells you nothing about any of this.
hth
David
Thanks, these links are good to know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply