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
johnnyg123Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick BogersConnect With a Mentor Datacenter platform engineer LindowsCommented:
Hi,

Not sure how ssis handle FTP i would use à script like below
I do know that FTP through Ssis requires remote path to end with \*.*

DECLARE @FTPServer varchar(128)
DECLARE @FTPUser varchar(128)
DECLARE @FTPPwd varchar(128)
DECLARE @SourcePath varchar(128)
DECLARE @SourceFiles varchar(128)
DECLARE @DestPath varchar(128)
DECLARE @FTPMode varchar(10)
 
-- FTP attributes.
SET @FTPServer = 'ftpserver'
SET @FTPUser = 'username'
SET @FTPPwd = 'password'
SET @SourcePath = '' -- Source path. Blank for root directory.
SET @SourceFiles = '*.LOG'
SET @DestPath = 'c:\Temp' -- Destination path.
SET @FTPMode = 'binary' -- ascii, binary or blank for default.
 
DECLARE @cmd varchar(1000)
DECLARE @workfile varchar(128)
DECLARE @nowstr varchar(25)
 
-- Get the %TEMP% environment variable.
DECLARE @tempdir varchar(128)
CREATE TABLE #tempvartable(info VARCHAR(1000))
INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
SET @tempdir = (SELECT top 1 info FROM #tempvartable)
IF RIGHT(@tempdir, 1) <> '\' SET @tempdir = @tempdir + '\'
DROP TABLE #tempvartable
 
-- Generate @workfile
SET @nowstr = replace(replace(convert(varchar(30), GETDATE(), 121), ' ', '_'), ':', '-')
SET @workfile = 'FTP_SPID' + convert(varchar(128), @@spid) + '_' + @nowstr + '.txt'
 
-- Deal with special chars for echo commands.
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPwd = replace(replace(replace(@FTPPwd, '|', '^|'),'<','^<'),'>','^>')
select @SourcePath = replace(replace(replace(@SourcePath, '|', '^|'),'<','^<'),'>','^>')
IF RIGHT(@DestPath, 1) = '\' SET @DestPath = LEFT(@DestPath, LEN(@DestPath)-1)
 
-- Build the FTP script file.
select @cmd = 'echo ' + 'open ' + @FTPServer + ' > ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPwd + '>> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'prompt ' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@FTPMode) > 0
BEGIN
    select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@SourcePath) > 0
BEGIN
    select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
END
select @cmd = 'echo ' + 'mget ' + @SourcePath + @SourceFiles + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit' + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
 
-- Execute the FTP command via script file.
select @cmd = 'ftp -s:' + @tempdir + @workfile
create table #a (id int identity(1,1), s varchar(1000))
insert #a
EXEC master..xp_cmdshell @cmd
select id, ouputtmp = s from #a
 
-- Clean up.
drop table #a
select @cmd = 'del ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd

Open in new window

0
 
AlexPaceConnect With a Mentor Commented:
Is the g: drive local or mapped?  If mapped, perhaps it is not available in the user context in which the job executes.  

Also be aware that FTP uses two connections.  The initial control channel connection where you log in and authenticate and an second connection which is a data channel that opened only when a file or directory listing is transferred.  The exact port number of the data channel is negotiated on the fly during the session.  If this data connection is blocked it creates a situation where you can connect but not transfer files.  

The data channel comes in two flavors: active mode and passive mode.  In active mode, the client chooses the port and the server opens a connection to the client.  In passive mode, the server chooses a port and the client connects to the server.  Default firewall configurations typically favor passive mode.
0
 
johnnyg123Author Commented:
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?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johnnyg123Author Commented:
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
0
 
johnnyg123Author Commented:
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!!!!!!!!!
0
 
johnnyg123Author Commented:
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?????????????
0
 
AlexPaceCommented:
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)
0
 
johnnyg123Author Commented:
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" ;-)
0
 
johnnyg123Author Commented:
Thanks!     Between the 2 responses I got to the ultimate solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.