Downloads becoming corrupt while use FTP Task

I have a Package that downloads apprx 15 data archives from an FTP site using FTP Tasks.

My problem is that the data contained within these archives (.Z) are becoming corrupt after download/extracting. I have isolated the problem to the FTP File Task by manually downloading the archives myself from IE's built-in FTP client and disabling the downloads in the Control Flow. The Control Flow will then execute the extracting script and continue without any problems.

If I run the Package with the FTP Tasks enabled it will get to a random spot in the validation (I haven't been able to see any consistency with where it errors) of the flat files and error with either a truncation error or a (don't remember exactly what it was) error that talks about an unexpected end in the file. I believe this is because the data has become corrupt and has started skipping/excluding columns. I have opened the flat file and examined it, and sure enough, the data at the end seems to just end.

I have tried setting the FTP Connection to Use Passive Mode in the config file - doesn't work.

So, my question is: Has anyone encountered similar problems? If so, what did you do to fix the problem? I'd really like to use the FTP Task rather than reinventing it with a custom script.Are you downloading these files sequentially to the same local file? If yes, add File System Task that deletes the destination (local) file before downloading to it.

There is a known issue with FTP task - it does not delete the destination file before overwriting it with content of new file. So if one of the files is smaller than than previous one - the file will have garbage from older file in the end. This problem will be corrected by SP2, and the workaround is to delete the local file or use unique local names.sql

Doing the same replace on several columns?

In the derived column task you can choose each column and write an expression for each column. But when you need to do a <ISNULL(status) ? "0" : statusdato> on 40-50 columns it get kind of irritating. Is there a way easy to do the sam expression on a selection of columns like a sort of derived column task, where you write an expression and assign that to a selection of columns (otherwise this would be a wish :-) )

Sorry, there is no such feature. Sounds like a good candidate to enter as a suggestion.

Note: it would be possible to write code that programmatically does what you are looking for, by loading a package, finding the derived column transform, getting the input column collection, and setting the expression properties.

Thanks
Mark

|||Ascential's (now IBM) DataStage had a really nice interfaces for doing just what you've asked. while SSIS doesn't have this interface, it does have the Script Component, which I find much more flexible than DataStage's Transformer stage.

I had a similar dilemma with having a good number of my input fields requiring trimming. Following the lead of several custom component examples I got rid of the ProcessInput_Row (I think that's what's in there) and overrode PreExecute and ProcessInput with the following in a Script Componet, of type Transform.

The biggest drawback is having to select all the checkboxes on the Input Columns tab and set them all to ReadWrite, but with some keyboard skills, that can go pretty quickly.

I've not been a programmer in any of my previous lives, so please forgive the poor form:

Public Class ScriptMain
Inherits UserComponent

Private _inputColumnInfos As ColumnInfo()

Public Structure ColumnInfo
Public bufferColumnIndex As Integer
Public lineageID As Integer
End Structure

Public Overrides Sub PreExecute()
Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim _inputColumnInfos(input.InputColumnCollection.Count - 1)

For x As Integer = 0 To (input.InputColumnCollection.Count - 1)
Dim column As IDTSInputColumn90 = input.InputColumnCollection(x)
_inputColumnInfos(x) = New ColumnInfo()
_inputColumnInfos(x).bufferColumnIndex = input.InputColumnCollection.FindObjectIndexByID(column.ID)
_inputColumnInfos(x).lineageID = column.LineageID
Next
MyBase.PreExecute()
End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
Dim columnInfo As ColumnInfo

While (Buffer.NextRow())

For x As Integer = 0 To (_inputColumnInfos.Length - 1)
columnInfo = _inputColumnInfos(x)

Dim trxVal As String = Buffer.GetString(columnInfo.bufferColumnIndex).ToString().Trim

Buffer.SetString(columnInfo.bufferColumnIndex, trxVal)
Next
End While
End Sub

End Class

does the XML Task update XML files?

I'm using package configurations to store my server/database name in an XML File. I need to be able to dynamically change the database at runtime when I execute the package. Can I use the XML Task in another package to make a change to that xml file? I'm not familiar at all with XML, so I don't really know the syntax for XPath or XSLT or anything. Basically I'm just looking for an example of how to this with XML, but I haven't found anything on the web to explain this to me. BOL isn't very helpful with the XML Task.
I'm using the June CTP, but also have access to the Sept CTP.
Thanks,
-Andy

If you save the results as a file, yes.
K|||but can I overwrite the source file? Do you have any examples of this or point me to one? I don't see on in BOL or in the Samples that come with the CTP's.
Thanks,
-Andy|||

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde

does the XML Task update XML files?

I'm using package configurations to store my server/database name in an XML File. I need to be able to dynamically change the database at runtime when I execute the package. Can I use the XML Task in another package to make a change to that xml file? I'm not familiar at all with XML, so I don't really know the syntax for XPath or XSLT or anything. Basically I'm just looking for an example of how to this with XML, but I haven't found anything on the web to explain this to me. BOL isn't very helpful with the XML Task.
I'm using the June CTP, but also have access to the Sept CTP.
Thanks,
-Andy

If you save the results as a file, yes.
K|||but can I overwrite the source file? Do you have any examples of this or point me to one? I don't see on in BOL or in the Samples that come with the CTP's.
Thanks,
-Andy|||

badandy:

did you ever get over this problem? i have come across the same problem and not sure how to do it. If you did please let me know

Thanks

hegde

Does the Undo operation work ?

Hi ,

I was working in the SSIS designer and when I found that the DataFlow task I deleted was indeed something I needed, I tried to Undo the operation but Alas the option is not enabled :(

Is there a way we can undo the operations?

as such I guess there is no way u can undo any changes. best possible way, work in Foundation server and keep checking in whenever you feel you are completed.

Does the Undo operation work ?

Hi ,

I was working in the SSIS designer and when I found that the DataFlow task I deleted was indeed something I needed, I tried to Undo the operation but Alas the option is not enabled :(

Is there a way we can undo the operations?

as such I guess there is no way u can undo any changes. best possible way, work in Foundation server and keep checking in whenever you feel you are completed.