SSIS FTP Task Password (and other) Problems

Originally posted on SQL Server Central, but posting it here as well for my future reference. :)

I've had some problems with the SSIS FTP Task where it does not seem to make the connections correctly. I keep getting the error "Password is invalid" and all sorts of errors.

I don't really like to use the script task component because of it just creates another area of potential coding error in the project.

But I had to take control of the FTP task functionality myself in order to make it do what I wanted it to do.

I realised that when the FTP Task does a listing, the unix box returns not only the file name but the timestamp as well.
Eg,
Problem 1
directory on the unix box
/files
File1.trg
File2.trg

When listing from FTP Task: remote directory = /files
"10:00 File1.trg"
"12:00 File2.trg"

Now, when the ftp task tries to get these files, its not found, or throws some error.

Problem 2
Passwords. Using a batch file to script the FTP is not a good solution, because at the end of the day, your password is clearly visible in plaintext in the script. Not a good solution. Using the package configuration file (*.dtsConfig) is useless as it too stores passwords in plaintext (till now, ssis hasn't implemented encrypted configuration files). Don't even think of using enterprise library with SSIS, you'll run into major headaches.

Solution
I've since given up on using the FTP task for my FTP, but we can still use SSIS's FTP plumbing and with some extra coding, make a properly platform independent and secure FTP function.

I've removed the error checking and variable assignment to make things easier to read.
Steps:
1. Create a FTP Connection Manager in your SSIS Designer 'RightClick in Connections - New Connection... - FTP'.
2. Create a variable to store the password.
Setup the package to use configuration file to store the password variable (and other information you need).
3. Fill in the FTP information as needed. 3.
4. Write a simple .net application that does string encryption. I won't put code for this here. You can google how to do this easily.
5. Use this tool to navigate the xpath of the dtsConfig file to password variable's value and encrypt it. This way, the password is not plaintext on the dtsconfig file.
6. Create a script task to take the password variable's (as readwrite) value and 'decrypt' it, remember to use whatever method was used to encrypt it in your separate .net app. Assign the decrypted password back to itself.
6. Create a script task to do the FTP functionality.
Public Sub Main()
'TODO: assign variables here...
dim password as string = dts.variables("vPassword").value.tostring 'the decrypted password

'Get instance of the connection manager.
Dim cm As ConnectionManager = Dts.Connections("FTPConnMgr")
'Set the password property to the decrypted password
cm.Properties("ServerPassword").SetValue(cm, password)

'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connect to the ftp server
ftp.Connect()

ftp.SetWorkingDirectory(remoteDir) 'set the remote directory

Dim files(0) As String
files(0) = fileToGet 'eg. File1.trg

'Get the file
ftp.ReceiveFiles(files, localDir, True, True)

' Close the ftp connection
ftp.Close()


Dts.Events.FireInformation(0, context, "File " + fileToGet + " retrieved successfully.", Nothing, Nothing, True)
Dts.TaskResult = Dts.Results.Success

End Sub

So there you go. An ssis package that decrypts a encrypted password on a dtsconfig file, decrypts it at runtime, and doesn't use batch files that expose the password.

If you'd like to know how to do a batch get of all files in a directory, i can show in another post. Whenever you need to change the password, just use that separate .net app to modify the dtsconfig file. Nothing needs to be done on the package.

Dynamically modifying an SSIS 2005 package

There may be a time when you need to create a package with a data flow that needs to be modified at run-time. This means that at design time, you don't create any column mappings, or maybe a few standard columns like a 'CreateDate' or 'UpdateDate' columns, but the actual data columns are not known at design time.

This requirement creates some problems like:
1. SQL Server doesn't have the necessary columns to store the data.
2. To create columns in SQL, we need the data type and length (if applies) for each column.
3. SSIS 2005 user scripts can't be used to modify the SSIS in which it is contained.
and a couple of more issues that i can't remember anymore... haha...

Well one approach will be to write an application/service that will:
1. Update SQL table with new/modified tables
2. Load the package and create sql table mappings.
3. Execute the package, if required.

We'll take a look at each of these steps in more detail.
1. Update SQL table with new/modified tables
1.1 To update SQL table, we need a the column definitions ie column name, datatype, length. This can be obtained from the source table and written to a csv file or read directly from the source database's INFORMATION_SCHEMA.COLUMNS view. Here's a sample code:
select COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Client'
Note: if you're integrating data between different databases eg. UniVerse -> SQL, Oracle -> SQL, SQL -> Oracle, etc..., you may need a function that will 'map' the different datatype between the database types.

1.2 Once you have the column definitions, you can make the connection to the SQL database (I'm assuming its sql, you can do it with other databases as well) and create your database table's columns.
Note: You need to import the following namespace in your application to be able to do the database manipulation:
Microsoft.SqlServer.Management.Smo()
Microsoft.SqlServer.Management.Common()
Code example to acquire a connection to the database in your program:

Dim db As Microsoft.SqlServer.Management.Smo.Database
'Get connection settings
Dim DBServerName, DBUser, DBPassword, DBName As String
DBServerName = "MyServer"
DBUser = "sa"
DBPassword = "sa_password"
DBName = "MyDatabase"
Dim SrvConn As New Microsoft.SqlServer.Management.Common.ServerConnection(DBServerName)
SrvConn.LoginSecure = False
SrvConn.Login = DBUser
SrvConn.Password = DBPassword
Dim srv As New Server(SrvConn)
'Open Required Database
db = srv.Databases(MyDatabase)

To get to the table you want to edit, use the db.Tables(tableName as string) function

1.3 Write code to add the columns
Dim tb As Table = db.Tables(tableName)
Dim newDataType As SqlDataType
newDataType = DataType.VarChar(intDataTypeLength)
Dim newColumn As New Column(tableName, columnName, newDataType)
tb.Columns.Add(newColumn)
Your SQL table is now ready for mapping.

2. Now the more difficult part.
Import the following namespaces:
Microsoft.SqlServer.Dts.Runtime.Wrapper
Microsoft.SqlServer.Dts.Runtime
Microsoft.SqlServer.Dts.Pipeline.Wrapper

2.1 Load the package (i'm assuming its on filesystem)
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package
pkg = app.LoadPackage("C:\PackageToEdit.dtsx", Nothing)

2.2 Now that you have loaded the package, we now need to 'navigate' to the component that is the dataflow component for the table you're going to modify.
To modify a dataflow, we have to start updating the names from the 'top' of the flow and end at the 'bottom' of the flow. This is what's called as the 'pipeline' by Microsoft.
Here's a basic sequence of modification we'll make.
a. Update the source connection manager with column names
b. Update the data flow source component
c. Update the data flow destination component
We'll go into detail now...

2.2.a Load the source connection manager.
You should know the name of your connection manager during design time. Its always good to use a naming convention for your components so they can be parameter driven if necessary.
What you need at this stage:
i) a variable / array containing the column names to be updated
ii) the source connection manager name as used in the SSIS Designer
iii) the package variable (we already loaded earlier)
Steps:
i. Get the connection manager object from the list of package's connection managers. I'm using an example of a flat file connection manager.
Dim SrcConn As ConnectionManager = Nothing
'Get specific connection
SrcConn = pkg.Connections.Item(ConnectionName)
'Get the underlying connection object
Dim FlatConnMgr As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerFlatFile90
FlatConnMgr = CType(SrcConn.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerFlatFile90)
ii. Now we create a new column and add to the connection manager's columns collection
Dim NewCol As RTW.IDTSConnectionManagerFlatFileColumn90 = nothing
FlatConnMgr.Columns.Add(NewCol)
'Assign the new column's properties
NewCol.ColumnType = "Delimited"
NewCol.ColumnDelimiter = "~"
NewCol.ColumnWidth = 1
NewCol.DataType = Wrapper.DataType.DT_STR
NewCol.MaximumWidth = 1
NewCol.DataScale = 0
NewCol.DataPrecision = 0
name = CType(NewCol, RTW.IDTSName90)
name.Name =
name.Description =
NOTE : Data type incompatibility. Looks easy? Here's a problem.

SSIS datatypes and SQL data types are NOT the same. You need to do some 'mapping' between the SSIS data type and SQL datatype. We already know what the column's SQL data type is, because we used it to create the SQL column, remember? So we just need to have a function that can do some 'mapping' for us. For your convenience, i'll include what i use, and you can modify if necessary.
Public Function MapSQLtoSSISDataType(ByVal SQLDT As SqlDataType) As Dts.Runtime.DataType
Select Case SQLDT.SqlDataType
Case SqlDataType.BigInt
Return Wrapper.DataType.DT_I8
Case SqlDataType.Binary
Return Wrapper.DataType.DT_BYTES
Case SqlDataType.Bit
Return Wrapper.DataType.DT_BOOL
Case SqlDataType.Char
Return Wrapper.DataType.DT_STR
Case SqlDataType.DateTime
Return Wrapper.DataType.DT_DBTIMESTAMP
Case SqlDataType.Decimal
Return Wrapper.DataType.DT_DECIMAL
Case SqlDataType.Float
Return Wrapper.DataType.DT_NUMERIC
Case SqlDataType.Image
Return Wrapper.DataType.DT_BYTES
Case SqlDataType.Int
Return Wrapper.DataType.DT_I4
Case SqlDataType.Money
Return Wrapper.DataType.DT_CY
Case SqlDataType.NChar
Return Wrapper.DataType.DT_WSTR
Case SqlDataType.None
Return Wrapper.DataType.DT_STR
Case SqlDataType.NText
Return Wrapper.DataType.DT_NTEXT
Case SqlDataType.NVarChar
Return Wrapper.DataType.DT_STR
Case SqlDataType.NVarCharMax
Return Wrapper.DataType.DT_STR
Case SqlDataType.Real
Return Wrapper.DataType.DT_DECIMAL
Case SqlDataType.SmallDateTime
Return Wrapper.DataType.DT_DBTIMESTAMP
Case SqlDataType.SmallInt
Return Wrapper.DataType.DT_I2
Case SqlDataType.SmallMoney
Return Wrapper.DataType.DT_CY
Case SqlDataType.SysName
Return Wrapper.DataType.DT_STR
Case SqlDataType.Text
Return Wrapper.DataType.DT_TEXT
Case SqlDataType.Timestamp
Return Wrapper.DataType.DT_DBTIME
Case SqlDataType.UniqueIdentifier
Return Wrapper.DataType.DT_GUID
Case SqlDataType.VarChar
Return Wrapper.DataType.DT_STR
Case SqlDataType.VarCharMax
Return Wrapper.DataType.DT_STR
Case SqlDataType.Xml
Return Wrapper.DataType.DT_STR
Case Else
Return Wrapper.DataType.DT_STR
End Select
End Function
You can use this function to return the appropriate SSIS data type from the SQL data type you have on the database. Not perfect but you can use it as a base.

iii. Once you've done the above for all your new columns, your Source Connection Manager will now have the necessary columns to be accessed downstream in the data flow.

2.2.b Update the Data Flow Components
Information you need at this point:
i) The name of the data flow component to update
ii) All the names of the data flow components you want to update as used in the SSIS Designer.
iii) list of new columns to add.
Steps:
i) Get the data flow component object.
Dim DataFlowTaskHost As TaskHost
Dim DataFlowMainPipe As MainPipe
DataFlowTaskHost = CType(pkg.Executables(), TaskHost)
DataFlowMainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
We now have the main pipeline of the data flow (whatever you see in the 'Data Flow' in the SSIS designer)

ii) Now get the data SOURCE object (top of the data flow). I'm assuming this data source component is set up to use the data source connection manager we already updated.
Dim DFSource As IDTSComponentMetaData90
DFSource = DataFlowMainPipe.ComponentMetaDataCollection.Item(SourceComponentName)
'Create an Instance of the source component
Dim instDfSource As CManagedComponentWrapper = DFSource.Instantiate
Now we 're-assign' the connection manager for this component so that it retrieves the new values that was updated on the connection manager earlier.
DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections.Item().ID
DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections(SourceComponentConnectionMgrName))
instDfSource.AcquireConnections(Nothing)
instDfSource.ReinitializeMetaData()
instDfSource.ReleaseConnections()
Note: the reinitalizeMetaData step is very important as it updates the internal/external metadata to be seen by other components.

iii) Now we need to update the data DESTINATION object. In this example, i'm assuming its an OLEDB Insert object.
As for the source component, we need to get hold of the component's object
Dim cmDestination As IDTSComponentMetaData90
cmDestination = DataFlowMainPipe.ComponentMetaDataCollection.Item(DataFlow Insert Dest. Name)
Dim instDFDest As CManagedComponentWrapper = cmDestination.Instantiate
Reassign the connection manager
cmDestination.RuntimeConnectionCollection(0).ConnectionManagerID = pkg.Connections.Item(DestinationComponent ConnectionManager Name).ID
cmDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections(DestinationComponent ConnectionManager Name))
instDFDest.AcquireConnections(Nothing)
instDFDest.ReinitializeMetaData()
Get the input object of the component so that we can add the new columns.
Dim input As IDTSInput90 = cmDestination.InputCollection(0)
Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()
We need to map each column of the input object's to the external metadata columns of the 'upstream' component, which is our source. This will make the Insert component aware of the new columns.
For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
Dim vCol As IDTSInputColumn90 = instDFDest.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE)
instDFDest.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection(vColumn.Name).ID)
Next
to finalise the changes we need to reinitialise the metadata.
instDFDest.ReinitializeMetaData()
instDFDest.ReleaseConnections()
The 'ReinitialiseMetaData' will actually check the columns against the SQL table (using its assigned connection manager) and report any errors here.
Done! you've got your components updated with the custom data.
If you'd like to see the changes, save your package using the app.SaveToXml function and you can open it in the editor.
This is a 'rough' guide and you'll need to add and fine tune it to your purposes.
Let me know if you found it helpful.

What makes programming difficult?

... statements like this:

One difference between HTTP handlers and ISAPI extensions is that HTTP
handlers can be called directly by using their file name in the URL, similar to
ISAPI extensions

haha!

Posting source code / html in Blogger

Bummer, i tried to post some source code in blogger and it went haywire. I thought it was as easy as cutting and pasting. but noo! it 'parsed' my html and tried to run my script and display my <form>.

Well, the crucial step is to replace all the ampersands and 'greater/less than' signs to their html equivalent. It basically accomplished in three steps.

1. Change the & to "& amp ;" (without the space) in your code snippet.
2. Change the "<" to "& lt ;" (without the space)
3. Change the ">" to "& gt;") (without the space)

Now cut and paste it into your blog editor.

Alternatively, head on Centricle and let their conversion tool do it for you.

Javascript live clock

It came to pass, that I needed to display the time on a page (though i'm still wondering why because windows does that for you on the task bar). Obviously the solution would be javascript. Here's the code i used, maybe someone would find it useful.
Description of what's happening is after the code.

<HTML>
<script>
setInterval("setTheTime()", 1000);

function setTheTime () {
var curtime = new Date();
var curhour = curtime.getHours();
var curmin = curtime.getMinutes();
var cursec = curtime.getSeconds();
var time = "";
if(curhour == 0) curhour = 12;
time = (curhour > 12 ? curhour - 12 : curhour) + ":" +
(curmin < 10 ? "0" : "") + curmin + ":" +
(cursec < 10 ? "0" : "") + cursec + " " +
(curhour > 12 ? "PM" : "AM");
document.date.clock.value = time;
}
</script>
<body>
<form name="date">
<input type="text" name="clock" style="border: 0px" value="">
</form>
</body>
</html>;

Explanation:

setInterval("setTheTime()", 1000);

Set interval is a function that performs a task after an interval (in milliseconds) specified. It basically starts a 'thread' that keeps looping. Go here for more info on setInterval()

function setTheTime()
This function will calculate current time and update the necessary field / element on the page with the time.

var curtime = new Date();

This returns an object that represents the current date & time

var curhour = curtime.getHours();
var curmin = curtime.getMinutes();
var cursec = curtime.getSeconds();

self explanatory, right?

if(curhour == 0) curhour = 12;

the time is represented in 24hrs format, this statement converts the 00 hrs (which is 12AM) to the number 12. If you want to keep in 24 hrs, remark or remove this line.

time = (curhour > 12 ? curhour - 12 : curhour) + ":" +
(curmin < 10 ? "0" : "") + curmin + ":" +
(cursec < 10 ? "0" : "") + cursec + " " +
(curhour > 12 ? "PM" : "AM");

This statement forms the string that will be displayed. we'll take a look at it line by line.

(curhour > 12 ? curhour - 12 : curhour)

this is a conditional statement.
if the hour in 24hr format is more than 12, it will return the 12hr format equivalent which is obtained by deducting 12 from it. otherwise it will return the same value.

(curmin < 10 ? "0" : "") + curmin ;

another conditional statement that returns a 0 to be prepended to the minute if its less that 10.
ex. 9 will be returned as 09, 5 as 05 etc...

(cursec < 10 ? "0" : "") + cursec ;

same as the minute.

(curhour > 12 ? "PM" : "AM")

returns the AM or PM text based on the hour.

document.date.clock.value = time;

this is a straightforward statement that tells which object's value needs to be updated with the text.
in more complex pages, like .aspx pages, you may need to update a textbox or label. use the following code
Updating a textbox:
document.all["<%=txtTime%>"].value = time
Updating a label:
document.getElementById("<%=lblDateTime%>").innerText = time
Note : I update the innerText property instead of the innerHtml because on some browsers, updating innerHtml repeatedly can cause a memory leak.

This is all fine when your elements are all on the page.
If the element you want to update is in a user control, it creates a different problem because the "<%=txtTime%>" or <%=lblDateTime%> will fail. You see, when a user control is registered, each control on each usercontrol (there can be more than one of the same user control on a page) rendered will be given a unique ID that we don't know at design time. So when the javascript tries to update the object, it can’t find it because there is no object with that ID and the time is not updated.
To overcome this, we use another property of the element we want to update, which is called the .ClientID which is the unique ID of the control that has been rendered.

So the new text should be
textbox: document.all["<%=txtTime.ClientID%>"].value = time
label : document.getElementById("<%=lblDateTime.ClientID%>").innerText = time

As for myself, i just use the object.ClientID whether or not i'm using user controls to avoid any complication.

Hope that helps.

ViewState in user control / child user controls

So i have a page which loads a user control, call it ctrlA and ctrlA has a user control ctrlB, which is loaded at runtime when i click a button.
I load my page, ctrlA is loaded and everythings looks good. i hit my button, and ctrlB is loaded. No problem.
Now ctrlB also has a button, when i hit this, the page gives me an error saying "The cast is invalid". What in the world happened?!
I found the problem.
In ctrlA if have a property that reads viewstate("Mode").
I also have the same property in ctrlB. So when the page hits a postback, and the 'LoadViewState' event fires, the page seems to go bonkers when it (i assume) tries to read the second viewstate("Mode") into memory.
I renamed the viewstate variable to use a different name in ctrlB and everything went fine.