Setting the FTP password from external configuration sources in a SSIS package

SSIS(SQL Server Integration Service) is a great ETL(Extract Transform Load) tool from Microsoft.It has many useful features for integrating data from different sources.One of the handy feature is the "FTP Task" that enables downloading files from a remote FTP address. The "FTP Task" uses a "FTP Connection Manager" component that stores the FTP credentials(URL,User,Password..). Instead of hard coding the credentials into the component, we can use “Expressions” feature of SSIS to set those properties from configuration files,registry entries or environment variables. So we can set the FTP URL,port,user name from the configuration sources.Unfortunately and surprisingly there is no option in the “Expressions” list to set the FTP password.The “Server Password” option just does not appear in the “Expression” List.One work around of this problem is to use the “Script Task” component and set the “Server Password” property of the “FTP Connection Manager” component. The VB .NET code (SQL 2005 only supports VB .NET) snippet below does the following:

1.Accesses the “FTP Connection Manager” component.
2.Sets the “ServerPassword” property with the value of a package variable(“FTPPassword” here).The package variable can be set from any external configuration sources supported by SSIS(XML file,Environment Variable,Registry..).


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim FTPConnectionManager As ConnectionManager


FTPConnectionManager = Dts.Connections("FTP_Conn")


FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager,
Dts.Variables("FTPPassword").Value)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class


Note:The “Script Task” component must execute before the “FTP Task” component .

Adding a serial number field to the ASP .NET GridView

I was developing a page that displays some data from database in a ASP .NET GridView control through LINQ. The grid required to display a serial number field as the first column of each row.Since i was binding the result of a LINQ call(List<SomeObject>) to the grid, i had to add a new field in the "Entity/Model" Class itself and populate the data somehow before binding it to the Grid. I was not willing to add a "Serial #" field into the class only to display a serial number and was looking for a solution at the View Layer.Then i found this handy solution built into the "GridView" control itself.

<asp:gridview id="myGridView" runat="server" autogeneratecolumns="False">

<columns>
<asp:templatefield headertext="#" width="15">
<itemtemplate>
<%# Container.DataItemIndex + 1 %>
</itemtemplate>
</asp:templatefield>
</columns>
</asp:gridview>

The "Container.DataItemIndex" property does the trick and provides an index for each row item in the GridView. Since the index starts from 0, I only had to increase it by 1 to display a serial number to every row without altering the Data Access Layer. Quite useful isn't it ?