Using DTS to FTP data to mainframe

Question

Using DTS to FTP data to mainframe

Alan Gredell

Say Hey Kid

Points: 684
More actions
March 28, 2005 at 10:14 am

#164895

My customer wants to push data up from a file on the SQL Server box to our mainframe. We have gone the other way with no problem, but I am not sure how this can be accomplished. It is part of a larger set of objectives, and DTS looks like a natural way to accomplish his objectives. Can this be done? Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

mike gercevich SSCrazy Points: 2961 More actions · Answer 1

Yes, DTS can accomplish this by either using the FTP Task or by using a 3rd pary COM+ component that is available to an Active-X Script Task.. if you need more control than what the FTP Task offers.

Mike Gercevich

philcart SSC-Forever Points: 47794 More actions · Answer 2

Take a look at the Custom FTP task available at http://www.sqldts.com

--------------------
Colt 45 - the original point and click interface

EdVassie SSC Guru Points: 60448 More actions · Answer 3

If Unix System Services (USS) are enabled on the mainframe, then FTP is a recognised mainframe command. BTW, it is all but impossible to run a mainframe nowadays without having USS enabled, so you should be OK.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

pokitlok SSC-Addicted Points: 471 More actions · Answer 4

As Alan's request, I have re-packaged the codes of my previous work and shared with you all. My original FTP task is to daily download files from Unix server to Win 2K server via FTP. I think it is no big deal if the code is changed for uploading files. It is just simple change the FTP script.

The code below is basically divided into 4 parts: FTP script, FTP procdeure, FTP Log and SQL Job Script.

--- Create FTP Script Table ---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ftp_script]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ftp_script]

GO

CREATE TABLE [dbo].[ftp_script] (

[stID] [int] IDENTITY (1, 1) NOT NULL ,

[script_line] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

--- Create FTP Script ---

insert ftp_script(script_line) values ( 'LoginID' ) -- user login

insert ftp_script(script_line) values ( 'Password' ) -- user password

insert ftp_script(script_line) values ( 'cd /SvrDestFolder/' ) -- server-side folder

insert ftp_script(script_line) values ( 'lcd C:\CltSrcFolder' ) -- local folder

insert ftp_script(script_line) values ( 'ascii' ) -- change to ascii mode for download

insert ftp_script(script_line) values ( 'mget *.*' ) -- multi-copy files

insert ftp_script(script_line) values ( 'dir *.*' ) -- List files

insert ftp_script(script_line) values ( 'mdelete *.*' ) -- multi-delete files

insert ftp_script(script_line) values ( 'quit' ) -- end ftp

GO

--- Create Store Procedure ftp ---

/* The procedure should be executed by an administrator account.

** Otherwise, it may lead to failure due to command "master..xp_cmdshell"

** This command has to be executed by administrator. It is known problem of MS SQL

** In the procedure below, ftp commands are pre-stored in table ftp_script.

** You may modify and dynamically generated the ftp commands.

*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ftp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop proc [dbo].[ftp]

GO

create proc [dbo].[ftp]

-- Purpose: Execute an FTP script

-- Original Author: Andrew Zanevsky, 21st Sentry, Inc.

-- Website: http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/0BE771908BB82C4C852568E2006B0F73

-- Modified by: Pokit Lok

-- Version: 3

-- Date: 2002/12/09

-- Modification in Version 3

-- 1. Allow to change ftp script table

@ftp_para varchar(30) = ' ',

@ftp_server varchar(255) = ' ',

@script_table sysname = 'ftp_script'

as

set nocount on

declare @line varchar(255),

@cmd varchar(255),

@pipe-2 varchar(2),

@ftp_file varchar(255)

select @pipe-2 = '>',

@ftp_file = 'c:\~~ftp_temp_' +

convert( varchar, @@spid ) + '.ftp'

create table #script (

line varchar(255) not null )

exec( 'insert #script select script_line ' +

' from ' + @script_table + ' order by stID ')

declare script cursor

for select line from #script

-- declare script cursor

-- for select script_line from ftp_script

open script

fetch script into @line

while @@fetch_status = 0 begin

set @cmd = 'echo ' + @line + @pipe-2 + @ftp_file

exec master..xp_cmdshell @cmd

fetch script into @line

set @pipe-2 = '>>'

end

close script

deallocate script

set @cmd = 'ftp ' + @ftp_para + ' -s:' + @ftp_file + ' ' + @ftp_server

select @cmd

exec master..xp_cmdshell @cmd

set @cmd = 'del ' + @ftp_file

select @cmd

exec master..xp_cmdshell @cmd

go

--- Create FTP Result Table ---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ftp_result]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ftp_result]

GO

CREATE TABLE [dbo].[ftp_result] (

[rsID] [int] IDENTITY (1, 1) NOT NULL ,

[result_line] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

-- Create job to ftp data --

/* Put the script below in TSQL Command box of [Edit Job Step]:

insert ftp_result(result_line) VALUES(Cast( getdate() as varchar)) --- record the execute time into log table
insert ftp_result(result_line) VALUES('FTP download files') --- record the task name
insert ftp_result(result_line) exec ftp '-i', 'ftp.yourserver.com' --- execute the ftp procedure

The log table is to help you find out the cause of ftp failure.FTP failure may be due to several reasons, e.g. source files

not existing and network broked, which natual DTS is not able to show you enough information. You may further modify the script

to enhance your ftp log.

*/

Joe Porrazzo SSC Enthusiast Points: 123 More actions · Answer 5

"As per my experience, uploading file by natual DTS is so flexible. For example, the file name is not allowed to changed. "

The name of the file can be changed by using the Visual Basic Active X Script functionality within DTS. Just send the output of the text file destination icon to the active x icon and within place a script.

Hope this helps...script would be something like:

Function Main()

'Rename the output file with a datetimestamp (or anything else)

Dim fso, SourceFile, DestFile, theFile, theNow

Set fso = CreateObject("Scripting.FileSystemObject")

theFile = "e:\filedirectory\test.dat" 'whatever the file path is on the PC

theNow = now()

SourceFile = theFile & ".dat" ' or whatver the file extension is

DestFile = theFile & "_" & Year(theNow) & Month(theNow) & Day(theNow) & Hour(theNow) & Minute(theNow) &Second(theNow) & ".dat

fso.MoveFile SourceFile, DestFile

Main = DTSTaskExecResult_Success

End Function

pokitlok SSC-Addicted Points: 471 More actions · Answer 6

Yes, it is so flexible if the file is transfered across local folders or machines given the service account having enough premission.

But FTP is another case. It is because Windows does not provide a built-in FTP DLL, like "Scripting.FileSystemObject" for file copying. Thus, it cannot create a object for FTP in VBA of DTS. The developers have to use some work-around method or buy a third party FTP component.

philcart SSC-Forever Points: 47794 More actions · Answer 7

I really don't know what the fuss is about here. I've used the FREE dts FTP custom task from http://www.sqldts.com for months without problems.

We FTP files from about half-dozen locations, two of which provide multiple files. All of them have different filenames that are based on the file creation date.

Some global variables, a short ActiveXScript task, a dynamic properties task and everything runs perfectly.

--------------------
Colt 45 - the original point and click interface

Joe Porrazzo SSC Enthusiast Points: 123 More actions · Answer 8

I've been using the FTP functionality within DTS for 3 years FTPing a flat file from a Windows server to a Unix server without any issue (as long as you have a userid/password on the Unix machine).

Not sure what the issue is...maybe different scenario FTPing frm Windows to Windows machine??

Robert Price Right there with Babe Points: 763 More actions · Answer 9

Sorry I'm so late on this thread....

I get around the rights issue by creating a batch file on a local drive on that executes an FTP script. The batch file is generic and I pass in the server name, logon info and FTP script to run and call it with xp_cmdshell.

( ftp -n -s:drive:\FTPscriptname.txt servername userid password )

I have to maintain the FTP scripts that I use... but that can be done relatively easily. You have to maintain that info somewhere no matter how you accomplish your http://FTP.  Obviously, the FTP scripts also need to be on a local drive to your SQL Server. I deal with approximately 1700 FTP's to Windows and Mainframe computers on different domains ( put and get ) every weekend this way. I dynamically generate new scripts when new tables are added. The entire process is table driven and runs with very little human involvement I found DTS to be unwieldy for this many FTP tasks...

Hope this is somewhat helpfull...

Robert

Alan Gredell Say Hey Kid Points: 684 More actions · Answer 10

Thanks to all who posted, I have enough information to chew on now! I am quite new to SQL Server, and am constantly astonished at the depth and breadth (esp. "depth"!) one can go to use this product. I am more of a classic DBA than a programmer, so have not thought about programming for myself in many years. It's becoming more and more clear that that must change! Thanks again to all...