Link to home
Start Free TrialLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

Ftp task ssis 2008

I have been asked to download a single file from an ftp site on a daily basis



I did a bit of research and found there is an ftp task in ssis so thought I would use that

I created an ssis package that has an ftp task

when I click test connection to the ftp server, I get test connection succeeded message


(in file transfer tab)

ISlocalPAthVariable is set to false
Local path is set to a folder on the g drive (used ellipsis to select)
overwriteFileatdest is set to false

operation is set to receive files
istransferascii is set to false


ISRemotePAthVariable is set to false
Remotepath is set to a file on ftp server (used ellipsis to select)

nothing in expressions tab


When I run the ssis package through BIDS (2008) it says it ran successfully but I don't see the file in the destination location.  

Driving me nuts...seems so simple... Not sure what I am missing
ASKER CERTIFIED SOLUTION
Avatar of Patrick Bogers
Patrick Bogers
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of johnnyg123

ASKER

The drive is local

Not sure I follow what you were trying to tell me about the 2 connections

Is this something I configure in the connection manager itself?
Here is something interesting

I was connecting to remote desktop from my pc to the server, creating ssis package on the server (via remote desktop connection), running ssis package (via remote desktop connection)

Completion successful but no file showed up in the directory



Created mapped drive to destination path on the server on my local machine
(the same path that I had used in the server ssis package)


Created ssis package on my local machine,  ran ssis package

Completion successful AND file was in directory


Is difference because I used remote desktop connection?

Guess I am puzzled because not getting any error messages when running ssis package on server ... execution says everything successful
Ok...so I tried one other thing

In the file connection manager editor I have usage type as Existing Folder
for the folder I put in the unc path  (\\gvcanalytics\d$\test)

If I load package in bids from the server and run the package from a remote desk top connection on server it flashes yellow for like 2 seconds and turns green


If I load package in bids from the server on my desktop machine the file downloads fine
(it is a 5 gb file so it takes a little bit but still)

There has to be something small I am missing

Please Help!!!!!!!!!
I tried a script command containing the following:



 Try
            Dim sFolderNames() As String
            Dim sFileNames() As String
            Dim sFileName(0) As String
            Dim conMan As ConnectionManager
            Dim ftp_client As FtpClientConnection

            conMan = Dts.Connections("FTPConnection")

            ftp_client = New FtpClientConnection(conMan.AcquireConnection(Nothing))

            ftp_client.Connect()

            ftp_client.SetWorkingDirectory("schedules")

            ftp_client.GetListing(sFolderNames, sFileNames)
            For Each fileName As String In sFileNames

                sFileName(0) = fileName
                ftp_client.ReceiveFiles(sFileName, "D:\Test\", True, False)

            Next


            ftp_client.Close()

            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception

            Dts.TaskResult = ScriptResults.Failure
        End Try



there is one file in the schedules directory on the ftp site

When I debug, it loops through the for each loop and sees the file.  (This tells me that it is able to reach the ftp server)

However, it looks like    ftp_client.ReceiveFiles(sFileName, "D:\Test\", True, False) is ignored

D:\test does not exist and yet I get no error  


Any one know why?????????????
I dunno why you didn't get an error passing an invalid value for the second parameter unless it is because there is an invalid type in the first parameter... it is supposed to be an array of string so it looks like you could do away with the loop and just do something like:

ftp_client.ReceiveFiles(sFileNames, "D:\Test\", True, False)
I think I may have figured it out

Not sure why it didn't dawn on me sooner but.....


I installed an ftp client on the server and tried to download a file to the server

Low and behold I got a

550 Requested file is prohibited by url filtering policy error

After conversation with network folks I learned that since server was in DMZ ... websense policies were being applied.  They had to make a change to the ftp policy


Only thing I can think is that script and ftp task were saying "hey...you told me to connect to ftp site and request file download  and that's what I did"   "I can't be responsible for checking to see if the request was successful too" ;-)
Thanks!     Between the 2 responses I got to the ultimate solution