DTS FTP Task From Mainframe Problem

[KevinSql]

I am trying to use the FTP Task within a DTS Package to copy a file from the Mainframe (OS390). We have SQL2000 with SP2 running on NT. This process works when I do it manually with ftp. At first, when I would execute the package it would just hang like it is copying but it would never end.

Then, I added a Dynamic Properties Task to the package. I used the Dynamic Properties Task to set up a Constant with the actual name of the Source File located on the Mainframe. When I ran this I received "Invalid string format for source file name".

Next, I removed the Dynamic Properties Task from the DTS Package, saved the package, and executed it. The package executed successfully, but when I looked in the folder on my hardrive the file that was supposed to be copied was not there. In order to get the package to execute successfully it appears that I have to go through the following process: add the Dynamic Properties Task, set up a constant to change the Source File name, save the package, delete the Dynamic Properties Task, save the package and then execute it.

Also, on one execution of this package, it brought across the file from the Mainframe to my hard drive but the file was in Binary format.

What do I have to do to get the FTP Task within a DTS Package to work? Would you specify in detail exactly how the Dynamic Properties Task needs to be set up? How can I specify how to change the file format from Binary to ASCII in the DTS Package?

Thanks, in advance.

[kevinsql]

FYI,

A co-worker found the article below for me. So, when using the FTP Task within a DTS Package --- the files are transferred in Binary mode only. I will just use the other method (DTS Execution Process Task to call a .bat file which calls a .txt file that contains the ftp commands).

Thanks, Kevin


-----Original Message-----
Subject: http://msdn.microsoft.com/library/en...asp?frame=true (see Highlight)


Data Transformation Services File Transfer Protocol Task New Information - September 2001. The File Transfer Protocol (FTP) task lets you download data files from a remote server or an Internet location as part of a Data Transformation Services (DTS) package workflow. Files are transferred in binary mode only. By including an FTP task, your DTS package can: · Copy directories and data files from one server directory location to another, before or after performing data movement and transformations. · Log in to a source FTP location and copy files or packages to a destination directory. The transformed data or package becomes available on an FTP site for download over the Internet, as part of the process of shipping the information to a data warehouse. · Schedule the download of different files on a regular basis. By using the FTP task in conjunction with a Dynamic Properties task, you can change the names of files to be downloaded, according to properties you specify (for example, a date or the value of a run-time global or environment variable). Note If you experience a problem using the FTP task, use ftp.exe in a command prompt to help identify the problem, as both the FTP task and ftp.exe use the same connection method. To add a DTS task to a DTS package Enterprise Manager <MS-ITS:howtosql.chm::/ht_dts_task_5ws2.asp>Enterprise Manager <http://msdn.microsoft.com/library/en..._task_5ws2.asp> See Also DTSFTPTask Object <http://msdn.microsoft.com/library/en...tasks_79ex.asp>