Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ssaresky
Starting Member
4 Posts |
 Posted - 2013-01-24 : 04:21:45
|
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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-01-24 : 13:33:40
|
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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 00:04:52
|
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/
|
|
|
|
ssaresky
Starting Member
4 Posts |
Posted - 2013-01-25 : 04:03:58
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 04:07:06
|
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/
|
|
|
|
|
|
|
Reading CSV file trough network (pass required)