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 2005 Forums
 SSIS and Import/Export (2005)
 Reading CSV file trough network (pass required)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssaresky
Starting Member

Spain
4 Posts

Posted - 01/24/2013 :  04:21:45  Show Profile  Reply with Quote
Hi,

The requirement is quite basic (I guess) but I'm unable to find a satisfactory solution to my problem.

Basically, we have a CSV file with data we'd like to upload into a given table once a month using a database job. We have no problem with that as long as the file is stored in the same server than the SQL2k5 server. But, actually, the file is left by the mainframe into a secure folder inside our intranet. That folder is also published as an FTP.

The file path would be \\HOSTNAME\FOLDER\SUBFOLDER\MYFILE.CSV
The FTP path is ftp://IPADDRESS/SUBFOLDER/MYFILE.CSV

For the file path I have a LANID and password with the required access to it (I've checked it trough Windows Eplorer).
FTP also has an userid and password known by me.

As said, I have no problem operating the file trough TSQL statements or SSIS as long as the file is stored locally (OPENROWSET does not work for us due a known compatibility issue we cannot fix at the moment).

So, I'll appreciate any suggestions on how I could access this remote file using at the same time the ID and Password supplied to my by the network admin.

As you might guessed already, I'm not a SQL Adminitrator and I don't have one here so thank you in advance for baring with me.

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 01/24/2013 :  13:33:40  Show Profile  Visit jackv's Homepage  Reply with Quote
I'm assuming you're using SQL Server Agent to manage the job? If so , can you confirm that the userid security used for the job - has the relevant underlying privileges to access the network file.
What account are you using to start up the SQLAgent Service?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Edited by - jackv on 01/24/2013 13:34:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2013 :  00:04:52  Show Profile  Reply with Quote
quote:
Originally posted by ssaresky

Hi,

The requirement is quite basic (I guess) but I'm unable to find a satisfactory solution to my problem.

Basically, we have a CSV file with data we'd like to upload into a given table once a month using a database job. We have no problem with that as long as the file is stored in the same server than the SQL2k5 server. But, actually, the file is left by the mainframe into a secure folder inside our intranet. That folder is also published as an FTP.

The file path would be \\HOSTNAME\FOLDER\SUBFOLDER\MYFILE.CSV
The FTP path is ftp://IPADDRESS/SUBFOLDER/MYFILE.CSV

For the file path I have a LANID and password with the required access to it (I've checked it trough Windows Eplorer).
FTP also has an userid and password known by me.

As said, I have no problem operating the file trough TSQL statements or SSIS as long as the file is stored locally (OPENROWSET does not work for us due a known compatibility issue we cannot fix at the moment).

So, I'll appreciate any suggestions on how I could access this remote file using at the same time the ID and Password supplied to my by the network admin.

As you might guessed already, I'm not a SQL Adminitrator and I don't have one here so thank you in advance for baring with me.




if you've ftp access to remote folder use FTP task in SSIS

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

Go to Top of Page

ssaresky
Starting Member

Spain
4 Posts

Posted - 01/25/2013 :  04:03:58  Show Profile  Reply with Quote
Thanks! FTP did the trick. Unfortunatelly, it seams there are some problems while using SQL 64bits with Office 32bits installed in the same server and accessing a different server with a 32bits OS as well.

Thank you both.

Sebastian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2013 :  04:07:06  Show Profile  Reply with Quote
quote:
Originally posted by ssaresky

Thanks! FTP did the trick. Unfortunatelly, it seams there are some problems while using SQL 64bits with Office 32bits installed in the same server and accessing a different server with a 32bits OS as well.

Thank you both.

Sebastian



you can configure SSIS to run in 32 bit mode

see

http://visakhm.blogspot.in/2011/11/excel-2010-export-import-issue-in-64.html

http://visakhm.blogspot.in/2011/12/dtexec-issues-running-ssis-packages-in.html

------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000