Ftp task ssis 2008

Posted on 2013-12-27
Last Modified: 2016-02-10
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
Question by:johnnyg123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
LVL 23

Accepted Solution

Patrick Bogers earned 250 total points
ID: 39742629

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
    select @cmd = 'echo ' + @FTPMode + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'lcd ' + @DestPath + ' >> ' + @tempdir + @workfile
EXEC master..xp_cmdshell @cmd
IF LEN(@SourcePath) > 0
    select @cmd = 'echo ' + 'cd ' + @SourcePath + ' >> ' + @tempdir + @workfile
    EXEC master..xp_cmdshell @cmd
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

LVL 16

Assisted Solution

AlexPace earned 250 total points
ID: 39742837
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.

Author Comment

ID: 39746202
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?
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


Author Comment

ID: 39746353
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

Author Comment

ID: 39746829 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!!!!!!!!!

Author Comment

ID: 39747079
I tried a script command containing the following:

            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.GetListing(sFolderNames, sFileNames)
            For Each fileName As String In sFileNames

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



            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?????????????
LVL 16

Expert Comment

ID: 39747166
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)

Author Comment

ID: 39748522
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 " 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" ;-)

Author Closing Comment

ID: 39748534
Thanks!     Between the 2 responses I got to the ultimate solution

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question