ftp task to network drive?

Hi I have a simple ftp task that downloads some files from an ftp to a local directory. That package run as a sheduled job from the sql 2005 server without a problem so far.

Now I whant to have the same task but to download the files to different mapped network drives , so I mapped the drives then I changed the paths to the config file but since then the job fails...

is it possible to do that or I did something wrong?

Thanks
g

Drive mappings are local to the user profile, so trying to use them in unattended jobs which run under a service account context does not make too much sense. I would strongly advise you to use a UNC path instead, e.g. \\Server\Share\Folder\File.ext

You will need top ensure that the service account or job proxy account has access to the share, as well as the normal folder permissions required for local as well.

|||Hi Darren,

Thanks a lot for the reply!!

I try the UCN path from the BI studio and it works fine but since I run it as a job faild. It must be something with the access to the share as you already said but since I am a newbie I don't know where exaclly to look for the problem... I am not running the job from a proxy do I have to create one? And if yes could you let me know for the process ?

As about the folder permissions I ve set full permission to "Everyone".

Thanks in advanced
G
|||

There is no need for a proxy account. By default your job will run under the context of the SQL Server Agent service account. Make sure you know what this is, and then check if that account has access to the share.

FTP Task Send error

I cannot seem to get the FTP task to send files. I tested the process manually (not using ssis) and it works fine. I have tried many ways to transfer the files, using variables, including full paths, partial paths, etc. but I keep getting a "550 permission denied" error. I have rechecked all variables. Has anybody else had trouble sending files with the FTP task?

Do you have access (or can someone GET access) to the FTP logs on the server to see who it thinks you are connecting as?|||

I am checking with them but it seems strange. I have set the user and password using a configuration file and I am getting the following error:

[Connection manager "MyFTPConnection"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Type set to IMAGE. 227 Entering Passive Mode (xx,xx,xxx,xxx,xx,xx). 550 permission denied .

I have tested the configuration file to make sure the server name was being set correctly from the file and it was. I am assuming the password would be also. I can connect manually.

This really should not be such a struggle. I remember DTS did not have an upload FTP component. Maybe I need a 3rd party component. Does anybody know of one?

|||

Looks like I'm connecting but the required slash is causing a permissions problem on the server. Anybody know how to solve this? The task won't let me send a file without a slash.

[FTP Task] Error: Variable "RemoteFile" doesn't start with "/".

sql

FTP Task fails to execute

Hi everyone:

I have a very simple FTP Task that is supposed to grab two files from an ftp server and save them locally on my machine. The problem is that when I test the connection everything works okay (I know that this connection "hides" the password when seen on the Connection Manager Editor, so I re-enter it everytime I want to test the connection). I can see in the Task property that the username and passwords are set, but regardless I always get this error message:

Error: 0xC002918F at Cost Files Transfer, FTP Task: Unable to connect to FTP server using "FTP Bread".

Any idea what could be worng?

Thanks

Idelso

for those of you who have the same issue. apparently is a bug in the Task. Simply put the password in a variable and reference it and it will work okay. The UI deletes the password and as such it can not make the connection.

|||

After you have set the password on the FTP Connection Manager Editor and click OK to commit the settings, did you re-open the FTP Connection Manager Editor and click OK again? This is the only way I can reproduce your problem.

Everytime you re-open the FTP Connection Manager Editor, we explcitly empyt out the password information. If you click OK without specifying tyhe password again, the empty password will be use.

So avoid the problem, either don't re-open it or click the "Cancel" button to avoid committing the empty password.

FTP Task error when no files found on FTP

Hi,

I have created a FTP task that logs into FTP server and receives files and scheduled it to run every 15min. However, it fails when there are no files on FTP. How would I check the if files exist? How can I catch the FTP task error and compare it to Hresults.NoFilesFound in a script task?

Thanks in advance for any help.

Here is a package that uses the WMI DataReader task to check a folder for files (Thanks Kirk).

Change the FOLDERTOLOOKIN variable to tell it what the folder name is, eg -> FilesDrop\\NewFiles

Change the DRIVETOLOOKIN variable to tell it what drive you're on, eg -> C:

The package uses a script task to show the fully qualified names of the files in the given folder.

The package outputs the names of the files to the FILESINFOLDER variable.

You can use a variety of ways to determine if the files are there.

This package does uses an expression on the precedence constraint to determine if the length of list of file names is greater than 0.

The script task only executes if there are files in the folder you specify. There is no mandatory script in this package. Only demo script to show a message box.

I'm not able to enclose the package so pasted it inline.

<save the following into a new package (.dtsx) >

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">DIGITALDARKROOM\Kirk</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">DIGITALDARKROOM</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">8/24/2005 2:31:04 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">5</DTS:Property><DTS:Property DTS:Name="VersionGUID">{48AF8158-4E9E-4541-A6AB-021099D0FE8A}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="ObjectName">WMI Connection Manager</DTS:Property><DTS:Property DTS:Name="DTSID">{9f10cc24-7919-4cec-85a0-cde454c7598d}</DTS:Property><DTS:Property DTS:Name="Description">Connect to the local machine</DTS:Property><DTS:Property DTS:Name="CreationName">WMI</DTS:Property><DTS:ObjectData><WmiConnectionManager ConnectionString="ServerName=\\localhost;Namespace=\root\cimv2;UseNtAuth=True;UserName=;"><PassWord Sensitive="1" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAqOymPn/hnk6nIeKlG26CGwAAAAAIAAAARABUAFMAAAADZgAAqAAAABAAAADf2RDrYmm04gJhrINpTzv0AAAAAASAAACgAAAAEAAAANKvr+kiwtHe1Gytu3jMIn4IAAAANMWO/krUx8MUAAAA5pODGqAD7RkcBRU1wcntq02Ne74&lt;</PassWord></WmiConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;2646&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="6" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="19209" y="13467" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper binary="01000000bc02803801000756657264616e61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="WMI Data Reader Task" left="2910" top="2646" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{14485c88-8b76-4591-86b2-5ed365bf4fff}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Script Task" left="2934" top="5651" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="4868" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="00080000041300008c040000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{933205d3-faf5-4a35-813a-c691178d0523}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;ddscontrol controlprogid="MSDDS.Polyline" left="4310" top="3411" logicalid="5" controlid="5" masterid="0" hint1="0" hint2="0" width="1458" height="2740" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobj&gt;
&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{e446313d-d87f-48a3-b358-c9a2df9d032e}" vartype="8" /&gt;
&lt;property name="Virtual" value="0" vartype="11" /&gt;
&lt;property name="VisibleAP" value="0" vartype="3" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;connector sourceid="3" destid="4" sourceattachpoint="7" destattachpoint="8" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;
&lt;point x="4709" y="3810" /&gt;
&lt;point x="4709" y="4730" /&gt;
&lt;point x="5368" y="4730" /&gt;
&lt;point x="5368" y="5651" /&gt;
&lt;/connector&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{586389B6-EA85-4C59-A7DA-240197FDAF89}</DTS:Property><DTS:Property DTS:Name="DTSID">{575D6A04-5089-4C0F-9C60-0AA14F70540A}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8"></DTS:VariableValue><DTS:Property DTS:Name="ObjectName">FILESINFOLDER</DTS:Property><DTS:Property DTS:Name="DTSID">{097AEA90-7896-4EAE-903C-B4D4A76A43E2}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">WMI Data Reader Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8">C:</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">DRIVETOLOOKIN</DTS:Property><DTS:Property DTS:Name="DTSID">{B7155D35-4B05-4C8E-948D-04CFC50A9BEA}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8">TEMP\\TEMP</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">FOLDERTOLOOKIN</DTS:Property><DTS:Property DTS:Name="DTSID">{F364298F-9E71-4D22-A0E3-394F6DDC73F7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:PropertyExpression DTS:Name="WqlQuerySource">"Select name from cim_datafile where path = \"\\\\" + @.[User::FOLDERTOLOOKIN] + "\\\\\" and drive = \"" + @.[User::DRIVETOLOOKIN] + "\""</DTS:PropertyExpression><DTS:Property DTS:Name="ObjectName">WMI Data Reader Task 1</DTS:Property><DTS:Property DTS:Name="DTSID">{14485c88-8b76-4591-86b2-5ed365bf4fff}</DTS:Property><DTS:Property DTS:Name="Description">WMI Data Reader Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><WMIDRTaskData WQLQuerySourceType="DirectInput" WQLQuerySource="Select name from cim_datafile where path = &quot;\\TEMP\\TEMP\\&quot; and drive = &quot;C:&quot;" DestinationType="Variable" Destination="FILESINFOLDER" OverwriteDestination="OverwriteDestination" OutputType="PropertyValue" WMIConnectionName="{9f10cc24-7919-4cec-85a0-cde454c7598d}"/></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Script Task 1</DTS:Property><DTS:Property DTS:Name="DTSID">{933205d3-faf5-4a35-813a-c691178d0523}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="FILESINFOLDER" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
OptionCompare = "0"
OptionExplicit = "0"
OptionStrict = "0"
ProjectName = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f/ScriptMain.vsaitem">
<![CDATA[' 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()
'
' Add your code here
'
Dim intLength As Integer = Dts.Variables("FILESINFOLDER").Value.ToString().Length
MsgBox("The number of bytes returned was : " + intLength.ToString() + Chr(13) + Chr(10) + Chr(13) + Chr(10) + Dts.Variables("FILESINFOLDER").Value, MsgBoxStyle.OKOnly, "WMI Data Reader Sample")
Dts.TaskResult = Dts.Results.Success
End Sub

End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{14485c88-8b76-4591-86b2-5ed365bf4fff}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{933205d3-faf5-4a35-813a-c691178d0523}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint 1</DTS:Property><DTS:Property DTS:Name="DTSID">{e446313d-d87f-48a3-b358-c9a2df9d032e}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">CheckingForFilesInAFolder</DTS:Property><DTS:Property DTS:Name="DTSID">{586389B6-EA85-4C59-A7DA-240197FDAF89}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||

Hi Anjan,

Thanks for your reply. But, I'm not sure how I can get WMI connection manager to connect FTP server and check if files exist. May be I'm missing something. Is it possible to capture the ExecResult of FTP task when it fails and do something in script task?

Please shed somelight.

Thanks.

|||

You could enable remote connections via WMI.

I guess you could do this with a script task, with just a few changes to the WQL query you can not only check local file system folders, but remote ones as well.

|||

Anjan,

Could you please be more specific on this becoz I'm not very familiar with SSIS? I was not able to figure out how to enable remote connections in WMI connection manager. I really appreciate your help on this.

Thanks.

|||

You might want to check the following

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/connecting_to_wmi_on_a_remote_computer.asp

http://www.ks-soft.net/hostmon.eng/wmi/index.htm

http://weblogs.foxite.com/stuartdunkeld/archive/2005/09/14/910.aspx

|||

SSIS newbie - There is a property in the FTP Task Editor called "StopOnFailure", set that to False. You could also set up a precedence constraint to check for Failure.

I'm not sure if this will help or not but it is something to try.

FTP Task error when no files found on FTP

Hi,

I have created a FTP task that logs into FTP server and receives files and scheduled it to run every 15min. However, it fails when there are no files on FTP. How would I check the if files exist? How can I catch the FTP task error and compare it to Hresults.NoFilesFound in a script task?

Thanks in advance for any help.

Here is a package that uses the WMI DataReader task to check a folder for files (Thanks Kirk).

Change the FOLDERTOLOOKIN variable to tell it what the folder name is, eg -> FilesDrop\\NewFiles

Change the DRIVETOLOOKIN variable to tell it what drive you're on, eg -> C:

The package uses a script task to show the fully qualified names of the files in the given folder.

The package outputs the names of the files to the FILESINFOLDER variable.

You can use a variety of ways to determine if the files are there.

This package does uses an expression on the precedence constraint to determine if the length of list of file names is greater than 0.

The script task only executes if there are files in the folder you specify. There is no mandatory script in this package. Only demo script to show a message box.

I'm not able to enclose the package so pasted it inline.

<save the following into a new package (.dtsx) >

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">DIGITALDARKROOM\Kirk</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">DIGITALDARKROOM</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">8/24/2005 2:31:04 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">5</DTS:Property><DTS:Property DTS:Name="VersionGUID">{48AF8158-4E9E-4541-A6AB-021099D0FE8A}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="ObjectName">WMI Connection Manager</DTS:Property><DTS:Property DTS:Name="DTSID">{9f10cc24-7919-4cec-85a0-cde454c7598d}</DTS:Property><DTS:Property DTS:Name="Description">Connect to the local machine</DTS:Property><DTS:Property DTS:Name="CreationName">WMI</DTS:Property><DTS:ObjectData><WmiConnectionManager ConnectionString="ServerName=\\localhost;Namespace=\root\cimv2;UseNtAuth=True;UserName=;"><PassWord Sensitive="1" Encrypted="1">AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAqOymPn/hnk6nIeKlG26CGwAAAAAIAAAARABUAFMAAAADZgAAqAAAABAAAADf2RDrYmm04gJhrINpTzv0AAAAAASAAACgAAAAEAAAANKvr+kiwtHe1Gytu3jMIn4IAAAANMWO/krUx8MUAAAA5pODGqAD7RkcBRU1wcntq02Ne74&lt;</PassWord></WmiConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;2646&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;
&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="6" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="19209" y="13467" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;
&lt;font&gt;
&lt;ddsxmlobjectstreamwrapper binary="01000000bc02803801000756657264616e61" /&gt;
&lt;/font&gt;
&lt;mouseicon&gt;
&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;
&lt;/mouseicon&gt;
&lt;/diagram&gt;
&lt;layoutmanager&gt;
&lt;ddsxmlobj /&gt;
&lt;/layoutmanager&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="WMI Data Reader Task" left="2910" top="2646" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{14485c88-8b76-4591-86b2-5ed365bf4fff}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Script Task" left="2934" top="5651" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="4868" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobjectstreaminitwrapper binary="00080000041300008c040000" /&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{933205d3-faf5-4a35-813a-c691178d0523}" vartype="8" /&gt;
&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;shape groupshapeid="0" groupnode="0" /&gt;
&lt;/ddscontrol&gt;
&lt;ddscontrol controlprogid="MSDDS.Polyline" left="4310" top="3411" logicalid="5" controlid="5" masterid="0" hint1="0" hint2="0" width="1458" height="2740" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;
&lt;control&gt;
&lt;ddsxmlobj&gt;
&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/control&gt;
&lt;layoutobject&gt;
&lt;ddsxmlobj&gt;
&lt;property name="LogicalObject" value="{e446313d-d87f-48a3-b358-c9a2df9d032e}" vartype="8" /&gt;
&lt;property name="Virtual" value="0" vartype="11" /&gt;
&lt;property name="VisibleAP" value="0" vartype="3" /&gt;
&lt;/ddsxmlobj&gt;
&lt;/layoutobject&gt;
&lt;connector sourceid="3" destid="4" sourceattachpoint="7" destattachpoint="8" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;
&lt;point x="4709" y="3810" /&gt;
&lt;point x="4709" y="4730" /&gt;
&lt;point x="5368" y="4730" /&gt;
&lt;point x="5368" y="5651" /&gt;
&lt;/connector&gt;
&lt;/ddscontrol&gt;
&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{586389B6-EA85-4C59-A7DA-240197FDAF89}</DTS:Property><DTS:Property DTS:Name="DTSID">{575D6A04-5089-4C0F-9C60-0AA14F70540A}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8"></DTS:VariableValue><DTS:Property DTS:Name="ObjectName">FILESINFOLDER</DTS:Property><DTS:Property DTS:Name="DTSID">{097AEA90-7896-4EAE-903C-B4D4A76A43E2}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">WMI Data Reader Task; Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8">C:</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">DRIVETOLOOKIN</DTS:Property><DTS:Property DTS:Name="DTSID">{B7155D35-4B05-4C8E-948D-04CFC50A9BEA}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">User</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="8">TEMP\\TEMP</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">FOLDERTOLOOKIN</DTS:Property><DTS:Property DTS:Name="DTSID">{F364298F-9E71-4D22-A0E3-394F6DDC73F7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:PropertyExpression DTS:Name="WqlQuerySource">"Select name from cim_datafile where path = \"\\\\" + @.[User::FOLDERTOLOOKIN] + "\\\\\" and drive = \"" + @.[User::DRIVETOLOOKIN] + "\""</DTS:PropertyExpression><DTS:Property DTS:Name="ObjectName">WMI Data Reader Task 1</DTS:Property><DTS:Property DTS:Name="DTSID">{14485c88-8b76-4591-86b2-5ed365bf4fff}</DTS:Property><DTS:Property DTS:Name="Description">WMI Data Reader Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.WmiDataReaderTask.WmiDataReaderTask, Microsoft.SqlServer.WMIDRTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><WMIDRTaskData WQLQuerySourceType="DirectInput" WQLQuerySource="Select name from cim_datafile where path = &quot;\\TEMP\\TEMP\\&quot; and drive = &quot;C:&quot;" DestinationType="Variable" Destination="FILESINFOLDER" OverwriteDestination="OverwriteDestination" OutputType="PropertyValue" WMIConnectionName="{9f10cc24-7919-4cec-85a0-cde454c7598d}"/></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Script Task 1</DTS:Property><DTS:Property DTS:Name="DTSID">{933205d3-faf5-4a35-813a-c691178d0523}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="FILESINFOLDER" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
OptionCompare = "0"
OptionExplicit = "0"
OptionStrict = "0"
ProjectName = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c368a900ee8149faae12a8a8995e6029"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_a882e6d3ef7b4f7fa60dabb8cb385b8f/ScriptMain.vsaitem">
<![CDATA[' 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()
'
' Add your code here
'
Dim intLength As Integer = Dts.Variables("FILESINFOLDER").Value.ToString().Length
MsgBox("The number of bytes returned was : " + intLength.ToString() + Chr(13) + Chr(10) + Chr(13) + Chr(10) + Dts.Variables("FILESINFOLDER").Value, MsgBoxStyle.OKOnly, "WMI Data Reader Sample")
Dts.TaskResult = Dts.Results.Success
End Sub

End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{14485c88-8b76-4591-86b2-5ed365bf4fff}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{933205d3-faf5-4a35-813a-c691178d0523}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint 1</DTS:Property><DTS:Property DTS:Name="DTSID">{e446313d-d87f-48a3-b358-c9a2df9d032e}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">CheckingForFilesInAFolder</DTS:Property><DTS:Property DTS:Name="DTSID">{586389B6-EA85-4C59-A7DA-240197FDAF89}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||

Hi Anjan,

Thanks for your reply. But, I'm not sure how I can get WMI connection manager to connect FTP server and check if files exist. May be I'm missing something. Is it possible to capture the ExecResult of FTP task when it fails and do something in script task?

Please shed somelight.

Thanks.

|||

You could enable remote connections via WMI.

I guess you could do this with a script task, with just a few changes to the WQL query you can not only check local file system folders, but remote ones as well.

|||

Anjan,

Could you please be more specific on this becoz I'm not very familiar with SSIS? I was not able to figure out how to enable remote connections in WMI connection manager. I really appreciate your help on this.

Thanks.

|||

You might want to check the following

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/connecting_to_wmi_on_a_remote_computer.asp

http://www.ks-soft.net/hostmon.eng/wmi/index.htm

http://weblogs.foxite.com/stuartdunkeld/archive/2005/09/14/910.aspx

|||

SSIS newbie - There is a property in the FTP Task Editor called "StopOnFailure", set that to False. You could also set up a precedence constraint to check for Failure.

I'm not sure if this will help or not but it is something to try.

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?sql

FTP Task corrupts file

I have an ftp task that grabs files from a remote dir ( *.csv ). However, it seams that the ftp task is corrupting some of my files. Has anyone else seen this? Is there something I can do? (These are grabbed as binary)

For example, here is the original on the remote server:

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

But here is the result on the local machine. You can see that the last 4 lines are duplicated (plus the last character of the preceding line):

25316,<ACTUAL>,296,917,48,10,1,2006-03-29,UPLOADER
25319,<ACTUAL>,63060,106,64,10,1,2006-03-29,UPLOADER
25300,<ACTUAL>,63060,206,64,10,1,2006-03-29,UPLOADER
29743,<ACTUAL>,56060,106,96,10,1,2006-03-29,UPLOADER
29744,<ACTUAL>,56060,206,96,10,1,2006-03-29,UPLOADER
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER
R
25315,<ACTUAL>,261,607,48,10,1,2006-03-29,UPLOADER
29749,<ACTUAL>,45030,103,96,10,1,2006-03-29,UPLOADER
29750,<ACTUAL>,45030,203,96,10,1,2006-03-29,UPLOADER
29751,<ACTUAL>,63030,303,64,10,1,2006-03-29,UPLOADER

Looks like the task does not delete the existing file, so if you had a bigger file before (probably downloaded with previous package run), the task overwrites the start of the file with new information, but leaves the data at the end of the file. You can workaround this by deleting the file(s) at the destination before downloading new files.

I'll enter a bug on this.

|||

Makes sense. I will use your recommendation of deleting files before downloading the new ones.

Thanks!

|||

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

|||

wndii wrote:

I'm having a simlar problem.

These are the first 10 records of a CSV uploaded from an IIS server to a z/OS machine.

119447,20060907,15360,008-0347-00,04,10854
119457,20060907,15360,008-0393-00,04,10854
119447,20060907,7680,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,10854
119457,20060808,15360,008-0393-00,04,10854
119450,20060808,7680,008-0386-00,04,10854
119442,20060808,15360,008-0340-00,04,10854
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31,12443

These are the output records produced in z/OS.

119447,20060907,7680,008-0347-00,04,10854
119447,20060907,15360,008-0347-00,04,
119447,20060907,15360,008-0347-00,04,10854
119445,20060907,15360,008-0344-00,04
119445,20060907,15360,008-0344-00,04,10854
119453,20060907,7680,008-0390-00,04,
119453,20060907,7680,008-0390-00,04,10854
119457,20060907,15360,008-0393-00,04,
119457,20060907,15360,008-0393-00,04,10854
119442,20060808,15360,008-0340-00,04
119442,20060808,15360,008-0340-00,04,10854
119450,20060808,7680,008-0386-00,04,
119450,20060808,7680,008-0386-00,04,10854
119457,20060808,15360,008-0393-00,04,
119457,20060808,15360,008-0393-00,04,10854
120101,20060808,25600,019-0061-00,31
120101,20060808,25600,019-0061-00,31,10912
167747,20060808,15000,031-0052-00,31
167747,20060808,15000,031-0052-00,31,12443
164751,20060808,20000,031-0034-00,31

It appears that the input stream is not being handled correctly. I'm creating a file of 80 byte records. Trying to create a VarBlocked file gives same result.

Any suggestions?

Is this the same issue? Are you using an FTP task? I'm not seeing the whole process here.|||

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

|||

wndii wrote:

Is this enough? I am told that server is IIS. It certainly appears to me we are running WS_FTP (Ipswitch). Does that run under IIS? I've tried SITE TRAIL and SITE mode but server doesn't accept them.

Also, CSV file is being created using SAS, in case that's pertinent.

I appreciate your help on this.

220 securetxr.emersonclimate.com X2 WS_FTP Server 5.0.3 (1359104614)
EZA1701I >>> USER ZOSTEST
331 Password required
EZA1701I >>> PASS
230 user logged in
EZA1460I Command:
EZA1736I ASCII
EZA1701I >>> TYPE A
200 Type set to ASCII.
EZA1460I Command:
EZA1736I get poeta.csv //DD:FTPU1
EZA1701I >>> PORT 155,*****************
200 command successful
EZA1701I >>> RETR poeta.csv
150 Opening ASCII data connection for poeta.csv (67678 bytes)
226 transfer complete
EZA1617I 67678 bytes transferred in 0.100 seconds. Transfer rate 676.78 Kbytes/sec.
EZA1460I Command:
EZA1736I QUIT
EZA1701I >>> QUIT
221-Good-Bye

That's all well and good, but are you having a SQL Server Integration Services issue with respect to their FTP Task, or are you just generally having an FTP issue?

FTP Task and Temporary Internet Files

I have created a package within SQL Server SSIS which includes an FTP Task, deployed it to our SQL Server (2005 SP1) msdb database and am running this job under SQL Agent on Windows Server 2003. Due to company security requirements this job has to be run under a service account within SQL Agent. The problem with this is that even though a directory is specified within the FTP Task to place any downloaded files into, the files are first written to the TIF (Temporary Internet Files) directory of "Default User" which is on the system drive. Based on corporate standards the system drive (C:) on our servers are only configured with enough space for the OS and other system files. All of the files being transferred are compressed, but some are still well over 1GB in size. The result is that many of our downloads are failing due to the system drive running out of space.

I have attempted to run IE by using "Run As" with the service account credentials, and have changed the location of the TIFs to a different drive, rebooted and verified the settings. When the SQL Agent job was run again, the files were still being written to the "Default User" directory on the system drive. I also created a new template account with the TIFs pointing to a non-system drive and used the User Profiles functionality of System Properties to copy the new template account to "Default User", but still the files are being written to the system drive.

My questions are:

is there a way to stop the FTP Task from using TIF (i.e. just directly write the file to the location specified) is there a best practice around how to setup a service account and have it create a proper user profile that can be managed separate from "Default User" short of specifying during the OS install, is there a way to move the "Default User" profile directory to a different drive

In case anyone else runs into this situation, I thought I would describe what ended up happening.

The page file was moved off of the system drive; otherwise the server was locking up when the system drive ran out or space. Now that the page file is on a separate drive, once the system drive runs completely out of space during a download the FTP Task (or whatever is managing the FTP transfer in the background) just starts writing the file being downloaded to the specified location (apparently it's smart enough to stop caching without crashing once there is no space left). Once the file that filled up the system drive completes downloading then it is removed from the TIF; normally the files aren't removed from the TIF until the entire package finishes execution.

The only downside to this is that the time period between the system drive becoming full and the file download completing, there are difficulties with logging into the machine. The difficulty is that some error messages are displayed and not being able to use your user profile - the Default User profile is used instead.

|||I am experiencing a similar problem. Does anybody out there know how to address this issue please?

Chris Lund, Software Installation Limited, Manchester UKsql

FTP Task : Put a wait in the FTP task

Hi,

I have a scenario where I want the FTP task to connect to a particular FTP server and download files. But if the files are not available on the FTP server then wait for 10 mins and then try again. Continue this till 3:00 am in the morning and if the files are still not there then raise an error notification.

Do I have to create a custom task for this? Or can it be achevied by using the existing SSIS components?

Your guidance is required to start me in the correct direction.

Thanks for your time.

$wapnil

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1178124&SiteID=1

The forum search is your friend.|||

Thanks again Phil.

Will be trying this out now.

$wapnil

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.

|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.

|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

sql

FTP Task - Delete Remote files Issue

We are Downloading files from FTP Site using "FTP Task" and we need to remove those files after downloading.

We need to delete files of specific names, like "Names_*.TXT" (All the txt files and Names starting from "Names_") so We are using Delete Remote Files and Remote path we are specifying as "/Names_*.TXT"

This doesn't work its not able to delete the files so, We tried using "*.*" and also given the specific filename like "Names_A1.TXT" then also its not deleting the remote files.

Please let me know the solution.

Thanks,

Priaynk Gajera

Priya,

Do you know for sure you have permissions to delete that file? FTP through command line and see if you are able to delete the file.

-S

|||

Yes,

I am able to delete the files from ftp using command mode as well as by opening ftp site.

|||There are several threads on this problem in this forum. MS claims they cannot reproduce the problem.

It might be specific to a version of FTP or Linux/Unix or something.

What platform and FTP server are you running?|||

Yes

You are correct, I have checked the same thing with same project and its creating problem with Unix server only not with Windows server... Why dont Microsoft people check with Unix server?

|||This problem is specifically with the Unix servers only. The ftp task(File Delete) works fine with Windows FTP Server but doesn't work with UNIX server.

To resolve this issue I have used script task which deletes the files on the ftp server.

This code is to delete all the files which is having names with starting of specific characters and that can be modifies as per your requirement.

The code is mentioned as Below.

Imports System
Imports System.Data
Imports System.Math
Imports System.Net
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text.Regular"FTP_server").Value.ToString
Dim remoteFilePath As String = Dts.Variables("FTP_filePath").Value.ToString
Dim remotePath As String = Dts.Variables("FTP_root").Value.ToString

Try
Dim fWebRequest1 As FtpWebRequest = CreateWebRequest(WebRequestMethods.Ftp.ListDirectory, remoteServer, remotePath)
Dim fWebResponse1 As FtpWebResponse = CType(fWebRequest1.GetResponse(), FtpWebResponse)
Dim strFileName As String = Dts.Variables("FTP_file_name").Value.ToString

Dim dir As StreamReader = New StreamReader(fWebResponse1.GetResponseStream())
Dim fWebRequest As FtpWebRequest
Dim fWebResponse As FtpWebResponse
Dim aline As String

While Not (dir.EndOfStream)
aline = dir.ReadLine()

If aline.StartsWith(strFileName) Then
fWebRequest = CreateWebRequest(WebRequestMethods.Ftp.DeleteFile, remoteServer, remotePath + "/" + aline)
fWebResponse = CType(fWebRequest.GetResponse(), FtpWebResponse)
Dts.Events.FireWarning(0, "File Deleted on server" & remotePath, "Status Code: " & fWebResponse.StatusCode & " Status Description: " & fWebResponse.StatusDescription, String.Empty, 0)

fWebResponse.Close()
fWebRequest = Nothing
fWebResponse = Nothing

End If
End While

fWebResponse1.Close()
fWebRequest1 = Nothing
fWebResponse1 = Nothing

Catch ex As WebException
Dts.Events.FireWarning(0, "WebException", "Status Code: " & CType(ex.Response, FtpWebResponse).StatusCode & " Status Description: " & CType(ex.Response, FtpWebResponse).StatusDescription & " Message: " & ex.Message & " Stack: " & ex.StackTrace, String.Empty, 0)
End Try

Dts.TaskResult = Dts.Results.Success
End Sub

Private Function CreateWebRequest(ByVal method As String, ByVal uriHost As String, ByVal uriPath As String) As FtpWebRequest

Dim uBuilder As UriBuilder = New UriBuilder()

uBuilder.Scheme = Uri.UriSchemeFtp
uBuilder.Host = uriHost
uBuilder.Path = uriPath

uBuilder.Port = Int32.Parse(Dts.Variables("FTP_port").Value.ToString)

Dim fWebRequest As FtpWebRequest = CType(FtpWebRequest.Create(uBuilder.Uri), FtpWebRequest)

fWebRequest.Method = method
fWebRequest.UseBinary = False
fWebRequest.KeepAlive = False
fWebRequest.Timeout = -1
fWebRequest.Proxy = Nothing
fWebRequest.Credentials = New NetworkCredential(Dts.Variables("FTP_username").Value.ToString, Dts.Variables("FTP_password").Value.ToString)
Return fWebRequest
End Function
End Classsql