Script/FTP Task a mainframe destination

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Script/FTP Task a mainframe destination

Author

Topic

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-21 : 08:49:49

Hello,

Has anyone had any success sending or receiving file(s) from either Script or FTP task? I've Google and found examples and no luck for me. The main idea is to send a file from local PC/server to mainframe.

Username: imtheuser
Password: pwd
Source file: c:\myfile.txt
Destination: 'PROD.ABC.DAILY.BATCH'

I've used this workaround, SSIS Script Task but no good.

SQL Server
Feedback Workarounds
281893. SSIS FTP Task - Mainframe
When you try to connect to a mainframe (os / 390) to ftp receive a file you get an error message stating that the path does not begin with a "/".
Active feedback entered 6/7/2007 by EWisdahl

Entered by EWisdahl on 6/7/2007

Add a script task (as follows) to download the desired files...
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "myServer")
cm.Properties("ServerUserName").SetValue(cm, "myUserName")
cm.Properties("ServerPassword").SetValue(cm, "myPassword")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim files(0) As String
files(0) = "MyfileName"
ftp.ReceiveFiles(files, "C:\temp", True, True)

' Close the ftp connection
ftp.Close()

'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub
End Class

Thanks again.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-09-21 : 12:08:49

This should be doable using the FTP task, another option you have is using xp_cmdshell combined with T-SQl, which is what most people will recommend.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2007-09-21 : 12:22:10

I have success with xp_cmdshell. However (learning other way), SSIS/Script/FTP Task is what I want to know how since it is not friendly with mainframe destination transmission. Because of the "/" requirement you can not use FTP Task(something MS forgot to spend time on, I guess).

mr4100
Starting Member

6 Posts

Posted - 2007-09-25 : 09:56:16

no luck putting or getting files off of mainframe using ftp task so i used the ftp.exe windows command using the execute process task but first create a text file with logon info using put or get commands to perform your task on the server. call that text file in the task by using -s in the argument field. Example below:

Execute Process Task:
executable: C:\windows\system32\ftp.exe
arguments: -s:"C:\ftpscript.txt"

text file script i used:

open serveraddress
username
password
Ascii
put C:\File.txt
bye
exit

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-25 : 10:01:21

1. Create a *.bat file


ftp -s:W:\data\CompDB\ftpcmdDataUp.txt > W:\data\CompDB\ftpcmdDataUp.log

2. Create an ftp script


open ISOF1 
loginID
PWD
delete 'BXRL94.COMPDB.D070924.T0000001'
quote site cyl pri=1 sec=1 lrecl=144 blksize=0 recfm=fb retpd=30
put W:\DATA\COMPDB\DATA\T0000001.dat 'BXRL94.COMPDB.D070924.T0000001' 
quit

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Chinni
Yak Posting Veteran

95 Posts

Posted - 2008-09-03 : 15:37:36

can any one explain in detail

i created

Batch file

Open servername
username
passwd
literal site refm=fc lrecl=50 blksize=6000 wraprecord
put C:\Documents and Settings\Desktop\sampleData.txt" ‘RFGT.FTP.HDP.KS.CODML(+1)' (replace
quit

SCR:

ftp -s:"HDPextract.scr" >"resultsHDP.txt"

OR how to create .exe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources