December 30, 2009 at 10:19 pm
I've googled on this error for probably longer than I should have. I've tried the common fixes that were suggested but I'm still getting the error when trying to do a Bulk Insert task. I get the same error when running the Bulk Insert SQL script from SSMS.
Situation: I'm using SSIS on virtual Win XP installation. I've also tried this same scenario running from a reporting server I have setup.
I've got the SQL server and database setup in the Data Sources in the Solution Explorer. I've got the same information listed in the Connection Managers area.
The text file is on a file server. I've used the path mapped with a drive letter and I've used the UNC but I still get the same error. I'm logged in under my domain login which is a domain admin equivalent.
One suggestion was to make the SQL Server service run with a domain account. Done. Give that domain account access to the folder that contains the text file. I gave that domain account full access to that folder.
A second suggestion I read about was making sure that no other processes were using that text file. Done. I'm the only one that even looks at this file and it's not open. I've successfully deleted the file after every failed attempt to load it into a table just to make sure.
A third suggestion was to give the domain account that runs the SQL Server Service delegation rights at the domain level. Done. I'm still getting that same error.
So in short, from the Business Intelligence Studio on an XP virtual machine joined to the domain under a domain admin equivalent account, I'm trying to execute an SSIS package that imports information from a tab delimited text file sitting on a file server (also joined to the domain) to a table on a database sitting on yet another server. I've also tried to run the same package from a server with SSIS loaded that isn't on any sort of virtual machine software and I get the same results.
Any thoughts?
TIA,
John
December 30, 2009 at 10:27 pm
One other thing...before we upgraded to 2005, my bulk insert scripts in Enterprise Manager worked fine on SQL Server 2000. It was only after that we upgraded to 2005 that I've had this problem doing any sort of Bulk Insert. Our upgrade was 4-5 months ago.
----
Update:
I also just found another post that suggested to try and run the script as SA. That worked. It just doesn't want to run under Windows Authentication. Running Bulk Insert commands worked fine under Windows Authentication in SQL Server 2000.
What do I need to do to make it run under Windows Authentication?
December 31, 2009 at 2:33 am
It seems that you are not alone in having this problem. One solution might be first to copy the text file to a local drive on the server & then run the job.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 31, 2009 at 10:46 am
Phil Parkin (12/31/2009)
It seems that you are not alone in having this problem. One solution might be first to copy the text file to a local drive on the server & then run the job.
It looks like it runs fine when the text file is located on the SQL server under Windows Authentication.
Is there a trick I need to know about to make the SSIS bulk insert package connect to an actual file server to do this? I don't want to have to run this package as SA in order to grab the file from the file server.
December 31, 2009 at 11:00 am
Trick? 🙂
Why not use Windows Authentication - as desired - but have a File System task that runs first to copy the file to the local server? Then run your insert off that file.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 31, 2009 at 1:13 pm
Phil Parkin (12/31/2009)
Trick? 🙂Why not use Windows Authentication - as desired - but have a File System task that runs first to copy the file to the local server? Then run your insert off that file.
Well, that is a way to make it work but I'd rather not mess with putting text files on our SQL server. I'd rather keep them on the file server.
I just came across some other forum posts that talked about looking into using the setspn utility because I may have to add a service principal name. I've never worked with that before but I'll read up on it. This is what I'll be reading...
http://technet.microsoft.com/en-us/library/dd285465.aspx
It was mentioned to look at this issue on this page[/url] that I stumbled on about 30 minutes ago.
January 4, 2010 at 12:06 pm
This is something I've faced before. Since you're using Windows Authentication, the login ID you're using must have at least READ permission for the source folder. Evidently, the SQL Server account already has this permission, which is why 'SA' works.
June 24, 2010 at 12:55 pm
Sorry, I've had to drop this project to work on others but now I'm not back at it. Here are a few more observations...
SSIS tasks that allow me to run them under SQL authentication work. Nothing works under Windows authentication when even attempting a file transfer (or bulk insert) task in SSIS. The account I'm logged in as is a domain admin. Even using the domain administrator account gives me the same errors.
Bottom line is I can't perform any task that involves going to another server to put or retrieve information where Windows authentication is used. I can do SSIS tasks like Bulk Insert and File System tasks when the files are on the same database server but not when the files are on another server.
Yes I could create batch files for these types of tasks and use the Windows scheduler to move files that way but I'd rather take care of this issue so I can use SSIS everywhere it makes sense to than to have to create workarounds with batch files.
Any input would be greatly appreciated.
TIA,
John
June 26, 2010 at 2:18 pm
I believe the issue you are running into is a known issue - called the double-hop issue with Windows authentication.
To get this to work and allow Windows to authenticate to multiple hops, you need to enable Kerberos authentication which requires that SQL Server be assigned all of the possible SPN records as well as a few other settings.
If you search this site for Brian Kelley's article on Kerberos - it will outline everything you need to do to get this working.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 29, 2010 at 2:40 pm
This might be such a low level that it's assumed but when I've had file access issues it's because the account used to access the file is the account that the SQL services run under, not whatever one is assigned to the package. Now, this may have been changed since 2000 but it always tripped us up when accessing files.
August 5, 2010 at 9:40 pm
John,
I ran into this not too long ago. What I found was that, although the service account had full rights to the target folder, there was a folder higher in the directory structure that the service account could NOT access. For example, if my file were located at:
\\MyServer\This Directory\Dir2\MyFile.txt
My svc account had full rights to the Dir2 directory, but did not have any permissions on the "This Directory" directory, and my ETL would fail every time. When I updated permissions on "This Directory", it worked fine.
On my example, I intentionally added a space in the directory name because, after I resolved this problem, I could not duplicate the problem unless there was a space in the directory to which the service account had no access.
I know it's a little obscure, and admittedly the environment in which I experienced the above problem was a bit, um, different </politicallyCorrect>. You might experiment with the following:
-- Check each directory in the UNC path chain to ensure the service account has access to all of them.
-- Rename any directory with a space in it.
I'd be interested to hear any results of this testing, so I can know if what I saw was just an anomale or is a legitimate bug.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply