SQL Job Agent

[JefB]

I have a DTS Local Package that exports a query result as a ".txt" file to a secure ftp site.
The package works perfectly. However, if I set up a scheduled job to run this, it fails.
The history log says the following:
>>Executed as user: SQL_SVR_U. ...Run OnStart: Copy Data from XXXReferralUpdate to \\Harpo\SFTP\Verily\XXXReferralUpdateTrans2.txt Step DTSRun OnError: Copy Data from XXX6ReferralUpdate to \\Harpo\SFTP\Verily\XXXReferralUpdateTrans2.txt Step, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from XXXReferralUpdate to \\Harpo\SFTP\Verily\XXXReferralUpdateTrans2.txt Step DTS... Process Exit Code 1. The step failed.<<

I'm guessing the problem relates to the secure FTP site because I do not have a problem setting jobs to run to non-secure FTP sites.

Any suggestions on where to look next would be greatly appreciated.

JefB

 

[PrgrmsAll]

JefB:
How are you? I know this has been a year ago but I was wondering if you had any resolution to this? I am actually trying to set up what you described in your post: exporting a query result and a textfile to a secure FTP site. Can you describe how you went about this or even show me your code (if you used ActiveX)?

Thanks in advance!

 

[an0nyM0use]

PrgrmsAll,
I have been dealing with an issue along these lines lately. And while I still seem to be having some problems with it I believe that I am close to a solution and will post it here when I have it solved. The route that I am going is to create an ftp.cmd to execute. I found the ActiveX route to cumbersome and I couldn't seem to get much support on the forum here for this kind of package.

http://www.tek-tips.com/viewthread.cfm?qid=955760&page=1

-Faris
"Eagles may soar, but weasels don't get sucked into jet engines.

 

[JefB]

Frankly, I do not remember exactly what I did to get this to work, but this is what I do now:

(secure FTP already set up)

1) Use Export Wizard from selected database.
2) Select default values for Source
3) Select "Text File" for destination
4) Browse for destination path to previously set FTP site. You should be able to reach it through "My Network
5) Specify table or query.
6) Set format
7) Check "Save DTS Package"
8) etc

Once the DTS file is set up, you can find it in DTS|Local Packages. Execute from there or set a schedule.

If this does not work, you may have permission issues with the SFTP. Talk to the Network Administrator or DBA.

Hope this helps.

JefB