Ftp task ssis 2008

Posted on 2013-12-27
Medium Priority
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
  • 6
  • 2
LVL 23

Accepted Solution

Patrick Bogers earned 1000 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 1000 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?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


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
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!!!!!!!!!

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 "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" ;-)

Author Closing Comment

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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 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