Get Next Number: March 2012

Thursday, March 29, 2012

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 samples

I am using SQL Server 2005 - CTP June 2005 and I am trying to create a simple FTP task. After I create a new FTP Connection Manager and verify the connection, I drag an FTP Task object to the designer and set the FtpConnection property to my FTP Connection Manager. The designer is showing a red X in the FTP Task object with the following error:

Attempt to read message string 0xc002f313 failed with error 0xc02090f3. Make sure all message re...

Any ideas? I am also trying to locate some samples for using the FTP Task. Can anyone point me in the right direction?

Thank you.
Clay BenoitHi Clay
This is a validation error telling you to complete the properties in the FTP dialog, either select send or the receive option with complete valid paths
for example of FTP please try the http://www.sqlis.com/

Thanks|||Perhaps it's this error? http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=40987f12-a941-46b3-ad80-01f67ff25559

FTP Task question

Hi,

In the FTP Task, is there any way to set it so that once a file is successfully FTP'd down, it deletes the file on the ftp server?

We need to be able to do this so that on the next day's ftp, we don't download OLD files, ie) the one from the day before with the same name. Deleting the file immediately after successful download will ensure that this never happens.

Thanks

I have one possible approach that you could use to achieve this:

Have one FTP task that does the operation of "Receive Files". A second FTP task can be used to the operation of "Delete Remote Files" and the precedence constraint between the first and the second task could be that the second one executes on the first task returning success.

Thanks,

Bhargavi

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes, I see that option now.

What happens in the event that the file does not exist - it got deleted some other way? Then I can't move forward on a success constraint, as it will be a failure.

I suppose I could have two "paths" but that seems kind of klunky.

Is there a better way to handle this?

Thanks

|||

You could use Completion. However, if the file doesn't exist, you don't want to try to delete it, do you?

I'd probably put both the FTP Task to download the file and the FTP Task to delete the file in a sequence container. Use an Success constraint between the download task and the delete task, and a Completion contraint between the sequence container and the next task to be executed.

|||It's not "klunky", it's really pretty straight forward and self-documenting. If the retrieval is SUCCESSful, DELETE the file. If the retrieval is SUCCESSful...any other steps. Use the COMPLETION to move forward with any steps that do not rely on the retrieval.

FTP Task question

Hi,

In the FTP Task, is there any way to set it so that once a file is successfully FTP'd down, it deletes the file on the ftp server?

Thanks

I have one possible approach that you could use to achieve this:

Thanks,

Bhargavi

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes, I see that option now.

What happens in the event that the file does not exist - it got deleted some other way? Then I can't move forward on a success constraint, as it will be a failure.

I suppose I could have two "paths" but that seems kind of klunky.

Is there a better way to handle this?

Thanks

|||

You could use Completion. However, if the file doesn't exist, you don't want to try to delete it, do you?

FTP Task Problem

I am new to this task...In order to set up this task do i need to set any basic thing on my local machine.

If so Please let me know

Sureshv,
Please read the Books On-Line page on this task and then come back here with any further questions you might have.

http://msdn2.microsoft.com/en-us/library/ms137656.aspx|||

I have few question

server name= ?

Server por =?

username=?

password=?

if i am trying to connect to remote

|||

sureshv wrote:

I have few question

server name= ?

Server por =?

username=?

password=?

if i am trying to connect to remote

If you know how to use FTP, you know the answer to those questions otherwise you won't be able to use FTP at all and you should probably talk to whomever supports the FTP site you want to connect to.|||

What i meant is

IS server name=Name of remote server or machine(is it ip address)

server port =server port which remote uses

username=is it valid username with all the permission to access

Password= valid password

|||

sureshv wrote:

What i meant is

IS server name=Name of remote server or machine(is it ip address)

server port =server port which remote uses

username=is it valid username with all the permission to access

Password= valid password

Yes.|||

I am trying to use FTP task to a remote machine.I am trying to take a local file from my machine and place it over there using FTP.

I place the ip address in the server name and i tried server name too..

port 21

i have a remote account(with full control set up) too by which i login using remote desk top

But my connection fails

Please help..

|||Can you ftp to this site and upload a file using one of approximately 10,000 (I'm being a little facetious but its close) non SSIS ftp clients?
|||I have no idea of what u are talking.what do i need to do now..I am not an expert in the area...would u please help|||I'm trying to help, believe it or not. The question was, can you upload to the site with a non-SSIS ftp client first off, so at least we know the issue is specific to SSIS?
|||

so do u mean that my system via command promt has an ability to reach the remote by sending files.

If u are talking about this.I say system gets connected but what do we need to do to send a file.

Please let me know.

|||Please stop starting new threads for an issue you've already opened here.

I've merged this thread.|||Should FTP from command prompt work first in order to set up ftp task
|||

sureshv wrote:
Should FTP from command prompt work first in order to set up ftp task

YES!

How do you know everything is setup correctly if you don't know how to FTP?

That is, how have you verified that it is setup correctly and that you have full permissions?

FTP TASK PROBLEM

Dear all,

I have problem in my ftp task.For example,
I create two variable remotepath and localpath to set up remote and local path of ftp task. And the IsRemotePathVariable and IsLocalPathVariable is set to true.

I create two funtions to retrun the pathvalue. The return value

is like 'D:\A.TXT' , but in DTSX the varible value is changed to 'D:\\A.TXT'. So my ftp task fail.

How can I overcome this problem?

Thanks a lot.

Regards

wen

Replace all occurences of "\\" with "\" if its causing you a problem.

You can do this in an expression.

-Jamie

|||

It doesn't actually change the value. It is just that the undelying engine for SSIS is written in C and "\" is an escape character. To actually display the value of "\" it has to be preceeded by a "\". so to use/display it in a string it appears as "\\". For example \r actually means a carriage return.

As for your situation what error are you getting?

|||

when the path value is changed to "\\" , the error is as bellow,

Error: 0xC0029183 at FTP Task, FTP Task: File represented by "User::FTPPATH" does not exist.
Task failed: FTP Task

sql

FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

Very simple. I click "Test Connection" and it succeeds. If I click "Ok", then try to re-open the connection manager and test again it fails. The password text box is blank, but the properties window in BIDS for the FTP Connection Manager shows "*******" for the password.

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP Task not saving password in designer

I am using CTP 15 and have created an extremely simple FTP task. I enter three pieces of information:

- Server URL
- Username
- Password

This seems like a very basic problem, anyone else have any ideas? My FTP task that utilizes this FTP Connection Manager also fails with a "missing password" type error message.

Thanks,
Josh

This is true of passwords in any connection manager UI... once they're set, the runtime prevents its retrieval so the UI doesn't know if there's a password or not.

regards|||Even though it doesn't appear in the Connection Manager, you should be able to see it in asterisk in the properties sheet for that connection manager.|||

The problem of this is it works fine in Visual Studio execution. But after deploying in server the execution reveals that the password is missing. How can I pack the password in the deployment file.

Thanks,

FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

With the FTP task could i get a list of directories on the FTP server, and compare the folders with a result set returned from a SQL query. If any of the directories on the FTP site then i want to download each of the relevent directories and there contents to the local machine.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

We are using /n software SSIS Tasks (which are currently in beta) without any problems.
Actually, I am using the very feature you need - directory listing with the goal to FTP the latest file (based on the date and time)in the remote FTP directory. The file name is unknown upfront.
Their tech.support is responding through e-mails in timely manner.

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

|||I suppose you could use an Execute Process task and a Perl script to perform the actions you desire... But then you are still reaching outside of native SSIS functionality.|||Thank you one and all for your suggestions. I'll look into the /n software component and see if that can solve at least part of my problem. To be honest i figured i may have to implement this part as a scripted task anyway. I've got no problems in writing some VB.Net code to do the job.
Many thanks for all your help.

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' 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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Dim files(0) As String

files(0) = maxname

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

|||You may check our library SSIS+. I'm talking in particular about SSH Execute, which is secure shell command execute task. You can send a command to retrieve a list of files based on your criteria. After this you can use our secure SFTP task to download the file(s).

Regards,
Ivan

FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' 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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Dim files(0) As String

files(0) = maxname

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

Regards,
Ivan

FTP task limitations

Hi,

I'm thinking about using the FTP task in an integration i am developing, but before i do, i need to get an idea that what i want to do is possible.

Could someone give me an idea as to if this is possible via the task, or if indeed a script task would be better?

Many thanks,

Grant

/n software has a task library for SSIS.
Their FTP task supports remote directory list retrieval along with many other features which built-in SSIS FTP task doesn't have.
Here is the URL:

http://www.nsoftware.com/ssis/

Regards,
Yitzhak

|||The problem with nsoftware SSIS components is that they do not work...
Sadly it's beta - I was really hoping to have visual components for FTP and Email tasks.

So if those components dont work for you - you probably need to do some scripting in .Net|||

/n software folks said that they are planning to release production release relatively soon - in November...

Regards,
Yitzhak

Cheers,

Grant|||

Sorry to bump a LONG dead thread, but I was reading this and thought that I might as well answer since I too had been searching for something of this variety...

I used a script task to look for the folder listings as below:

' 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("..")

ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Dim files(0) As String

files(0) = maxname

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

Regards,
Ivan

FTP Task issue

The FTP Task I've created fails. It appears to be making the connection initially behind a firewall, but it suddenly switches from port 21 to port 3397. Anyone experience this issue? Since my firewall is restricting the connection to just port 21, I can't make a permanent connection.

FTP use 2 ports for it work - one for control connection (21) and one for data connection (any other, 3397 in your case, but may be other).|||When a ran a test comparison between the SSIS FTP Task and ftp command line, I only saw this difference when the SSIS FTP Task ran. Is this two-port usage specific to SSIS? I'm able to get out past the firewall using the ftp command without errors. I can't with SSIS.|||FTP use 2 ports by design (you can read RFC about it). Control connection used to transport commands and most simple responses, but file data transferred by data connection, which is open and live while particular transfer goes on.|||I ran a check again and did see windows command-line ftp sending the data transfer on port 20. Thanks.|||

Your problem is this: FTP normally uses ports 20 & 21. But, if you run in passive mode then the remote server will dynamically allocate a port number to you. So, the trick is to run in ACTIVE mode and then you will always run on ports 20 & 21.

If you are running on visual basic and I can let you have some source code to do this.

FTP Task in SSIS

Hello,

I m having an FTP Task in SSIS project, from where I have downloaded .csv file.

And also having a Processed Folder in which the processed .csv files are kept.

Main task is to download file from FTP and check , if the date of downloaded file is newer than file in processed folder, then only process that file. Else skip the processing

Can anyone help me out how to process about.

plz reply as early as possible.

Thanks & Kind regards ....

Aparna

I do not know if this is the best way to do this, but I would personally use the Foreach Loop container and the Script task. Loop over the folder into which the FTP task is dropping files, storing the file name in a package variable.

Then, inside a Script task contained within the Foreach Loop, use the System.IO.File class to compare the files retrieved by the FTP task with the matching files in the processed folder. Based on the comparison, set a package variable flag ("Newer"/"Older" or true/false or the like) in your script.

Then, back in your package's control flow, edit the properties of your precedence constraint(s) to execute the later tasks only if the flag variable is set to the correct value.

Does this help?

sql

FTP Task in a SQL Server Agent Scheduled Job - Corrupts Excel file?

We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.

I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.

In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.

Has anyone experienced this issue? Any inputs will be appreciated.

Just as an fyi, the excel file has a lot of vlookups.

Thanks,

MShah

The FTP task does not delete existing file when downloading new one with the same name (we have recently found it and planning to fix for SP2). So if the downloaded file is smaller than existing one, it overwrites the beginning of existing file with new content, but the remaining file content might be from previous file.

The workaround is to use File System Task to delete existing file before downloading new one.

FTP Task for SSIS missing

Hi Everyone,

When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.

Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.

ThanksRight-click the toolbox, Choose Items. Select the SSIS Control Flow page and have a look for any missing tasks, those not checked.

FTP Task failure in SQL Job

I have a SQL IS package, with FTP task. It runs fine when I am running from the client. When deployed it on the server as a SQL Job, it failes with error message

"Unable to connect to FTP server using 'FTP Connection Manager' (Name I used for the FTP connection in IS package)".

I tried to run the .dtsx on the server connecting to Intergration services. And I get same error message with little more details, "Password was not allowed".

With same USER_ID and Password and default port number I am able to connect to the FTP site with no problems.

Do I need to do it differntly making the FTP connection inside the IS package? What's the correct way of doing it ?

satya

When you deploy your package to SQL Server make sure you use Protection Level: "Rely on server storage and roles for access control"

|||Thanks for responding to my questions. I did what you suggessted and am still having the same issue.

Steps I followed:

1. Copy the .dtsx to server location from my local.
2. Imported the package to SQL server location with the suggested options.

Do you think, I am missing any thing still ?|||

Yes.
It gets corrupted when you copy.

You can either:
1. Deploy without copy

1. Set protection level to EncryptSensitiveWithPassword
2. Copy
3. Imported the package to SQL server location with options I suggested before.

FTP Task Failure

We are unable to get a file from a VMS FTP site. The specific error message is:

[ftp task] Error: File represented by "/VEQFFP.TR.MTSS.RATE" does not exist.

normal command line ftp and other ftp tools all succeed. Is anyone else experiencing this difficultly?

Thx in advance.Hello

Had problems with the FTP task opening more than one connection (this was ) to the server. Workaround was to use cmd ftp taking input from file. That was best practice at the time.||| Tongue Tied Have already used cmd files to accomplish this task. Want to use the new SQL FTP 2005 dtsx task. Since the Mainframe Server sets us into the root, I have not way to manuever to the directory to capture the file I need. Thx|||

Hi.

We are having the same problem.

Usually you don't need the "/" however SSIS is forcing you to put a "/" or it won't execute.

Have you found a workaround yet

Thank

|||I have the same problem which prevents pulling files off VMS. Even if I use an account that lands me in the correct directory upon connection, VMS won't accept the leading "/" that the SSIS FTP Task forces you to use. I'm thinking of using a Script Task to create my own FTP task (using the FtpWebRequest class from .NET 2.0), as the standard one simply isn't flexible enough.
|||I got the same error but it turned out that after browsing to the file to be downloaded using remote path, when I ran the task a configuration file was setting the ftp connection to a different server and then I received an error that the file did not exist.

FTP Task Failure

We are unable to get a file from a VMS FTP site. The specific error message is:

[ftp task] Error: File represented by "/VEQFFP.TR.MTSS.RATE" does not exist.

normal command line ftp and other ftp tools all succeed. Is anyone else experiencing this difficultly?

Thx in advance.Hello

Hi.

We are having the same problem.

Usually you don't need the "/" however SSIS is forcing you to put a "/" or it won't execute.

Have you found a workaround yet

Thank

FTP Task Failure

We are unable to get a file from a VMS FTP site. The specific error message is:

[ftp task] Error: File represented by "/VEQFFP.TR.MTSS.RATE" does not exist.

normal command line ftp and other ftp tools all succeed. Is anyone else experiencing this difficultly?

Thx in advance.Hello

Hi.

We are having the same problem.

Usually you don't need the "/" however SSIS is forcing you to put a "/" or it won't execute.

Have you found a workaround yet

Thank

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.

<?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<</PassWord></WmiConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><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"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2646</dwd:BoundingTop><dwd:Layout><dds>
<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}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000bc02803801000756657264616e61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<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">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{14485c88-8b76-4591-86b2-5ed365bf4fff}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<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">
<control>
<ddsxmlobjectstreaminitwrapper binary="00080000041300008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{933205d3-faf5-4a35-813a-c691178d0523}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<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">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="2" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{e446313d-d87f-48a3-b358-c9a2df9d032e}" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector sourceid="3" destid="4" sourceattachpoint="7" destattachpoint="8" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="4709" y="3810" />
<point x="4709" y="4730" />
<point x="5368" y="4730" />
<point x="5368" y="5651" />
</connector>
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></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 = "\\TEMP\\TEMP\\" and drive = "C:"" 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.