Using DTS to FTP data to mainframe
-
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.
-
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
-
Take a look at the Custom FTP task available at http://www.sqldts.com
--------------------
Colt 45 - the original point and click interface
-
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
-
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.
*/
-
"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
-
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.
-
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 -
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??
-
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
-
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...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply