Miscellaneous Ramblings on Hacking

July 24, 2007

Using WinZip within SSIS

Filed under: ETL, SSIS — Zack Bethem @ 11:30 pm

Windows comes with the ability to compress files. In practice, however, I found that the standard utility starts to choke when compressing larger flat files (greater than 2GB). To get around this problem within SSIS, I used the default WinZip program. There were some tricks to get it working, however.

Using a Process Task in the Control Flow of a package, I configured the following via Expressions in the Expression Editor:

  • set the WorkingDirectory to the expected location of the data file
  • set the Arguments to evaluate as: -min -a <fullpath>\<zipfile>.zip <fullpath>\<sourcefile>.txt

The Process Task Executable argument was set to the absolute path of the WINZIP32.EXE program (eg. C:\Program Files\WinZip\WINZIP32.EXE)

Advertisements

Automating FTP

Filed under: ETL, SQL Server Integration Services, SSIS — Zack Bethem @ 11:19 pm

Processing flat files are a common task when building a Data Warehouse. The need typically arises when integrating with 3rd-party providers or data outsourcers. A common integration technique is to use flat files with FTP as a transfer mechanism. One should use secure FTP, but SSIS out-of-the-box only supports the weaker, less-secure FTP.

When configuring the FTP connection, SSIS cannot store the login information. This is great if your final solution requires a human to push a button and enter the security information every time, but hardly ideal for us that like to automate things and sleep at night. To get around the problem, one must include a Script Task in the package’s Control Flow. The Script Task will set the connection properties of the FTP Connection automatically.

Here’s how you do it:

1. Add a Script Task within the Control Flow before the FTP

Sample Control Flow

2. Assign the following user defined variables as ReadOnlyVariables:

FtpServer – server connection

FtpPort – port number used for FTP connection

FTPuser – FTP user account

FTPpwd – FTP password for account

3. Add the following code to the Script Task to set the connection properties for the FTP Connection Manager:

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 Connection Manager”)

ftpConnectionManager.Properties(“ServerName”).SetValue(ftpConnectionManager, Dts.Variables(“FtpServer”).Value)

ftpConnectionManager.Properties(“ServerPort”).SetValue(ftpConnectionManager, Dts.Variables(“FtpPort”).Value)

ftpConnectionManager.Properties(“ServerUserName”).SetValue(ftpConnectionManager, Dts.Variables(“FTPuser”).Value)

ftpConnectionManager.Properties(“ServerPassword”).SetValue(ftpConnectionManager, Dts.Variables(“FTPpwd”).Value)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

July 14, 2007

Vista Upgrade – RDP to Windows 2003

Filed under: Uncategorized — Zack Bethem @ 5:35 pm

I was having difficulty after the Vista Business upgrade connecting to clients via RDP. I’m doing a lot of work these days from home. The setup is typical, using the RDP ActiveX control to connect to a remote machine.  This didn’t work, however, in Vista.  Instead, the page would slowly draw and eventually crash.

The solution was found! Thanks to this post.  I have no idea what these two little commands do, but it worked like a charm.

netsh interface tcp set global rss=disabled
netsh interface tcp set global autotuninglevel=disabled

Blog at WordPress.com.