SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 import from csv file error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lappin
Posting Yak Master

182 Posts

Posted - 09/24/2012 :  07:12:34  Show Profile  Reply with Quote
Hi I have a stored procedure which imports from a csv file and saves to a temporary table. The procedure runs fine when I run "exec myProc" but when I put the same "exec myProc" into a SQL Agent job I get an error.
Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"

I have also tried running job as a windows user who is member of local administrators group. I get same error except the user name is different in error message.

The folder containing the file is a shared folder and the local user and network service user have full permissions on the folder and on the share.

I've tried two different methods Bulk Insert and openrowset.
I have also removed the saving to temp table where I just read the first few lines of the csv file but still get same error.
Here is the way I read from csv file:


CREATE TABLE #MyTemp
(
ID int, Field1 nvarchar(5) , etc
)



BULK
INSERT #MyTemp
FROM 'C:\folder1\MyFile.csv'
WITH
(
FIRSTROW =2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'

)

---------------------------

INSERT INTO #MyTemp

select *
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'

,'select * from C:\folder1\MyFile.csv ' )

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/24/2012 :  13:25:15  Show Profile  Reply with Quote
is the csv path in your local machine or in server?
is sql server instance on your same machine or in a remote machine/server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

lappin
Posting Yak Master

182 Posts

Posted - 09/25/2012 :  06:21:52  Show Profile  Reply with Quote
Hi, yes the folder is local to the server. The path is C:\myFolder where "myFolder" is windows folder name and the share name. As I mentioned above executing the procedure works but not from within agent
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/25/2012 :  12:56:10  Show Profile  Reply with Quote
can you try creating a proxy account and configure sql job to use it instead. give account permissions to shared folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000