23 Mar 2006

Installing SQL Server 2005 Express with Remote Connections.

I have managed to set-up a SQL Server 2005 Express instance, on a windows-XP machine, with remote connections enable. The actual installation of the SQL Server 2005 express instance was straightforward, but enabling the remote connections was problematic.
I found that I was not the only person. In the end I found these steps worked for me:
  • Open up the SQL Server Surface Area Configuration Manager
    • Click on Surface Area configurations for services and connections.
    • Click the remote Connections entry on the tree, select local and remote connections (TCP/IP) . Click OK.
  • Open the SQL Server Configuration Manager
    • Select SQL Server 2005 Network Configuration/Protocols for the instance.
      Right click TCP/IP, select properties.
    • Click the IP Addresses tab, change the Enable flag on the IP addresses to YES.
    • In the IPAll section, copy the port value of the TCP Dynamic Ports to the TCP Port.
    • Clear the TCP Dynamic Port value.
    • Click Okay.
  • Open up Control Panel/Firewall.
    • Click Exceptions tab.
    • Click add port button.
    • Enter the TCP port number specified in the IPAll/TCP Port section above.
  • Restart the SQL Server Service instance.
  • Restart the SQL Server Browser Service.

14 Mar 2006

SSIS Creating Synchronous Transform Component

Do you need to write a custom component to transform input data, and disregard this input data, instead, replacing it?
That's was what I needed to accomplish with my component. It takes data from the input buffer and performs a lookup via an external API. If there is a match, send the result data down a "match" output otherwise send the input data down a "unmatch" output. The key here is that the "shape" of the output was changing. In other words, the metadata of the output was very different from that of the input.I also want my component to be synchronous (meaning synchronous, I mean that the component takes a row, processes it and then immediately sends it down an output) and not asynchronous (meaning that it reads all the data and then processes the data in an internal buffer). Now from what I was reading and seeing this was what I thought SSIS meant by synchronous and asynchronous components, but I was wrong!
  1. An asynchronous component can eitherRead all the data in the buffer and cache it. Then after reading all the records process them and then send the data out or
  2. It can read a row of the input; process that data in place without caching it and then send it down the output.
So, I wrote my component to be an asynchronous component but behave like a synchronous component. To do this I create my component outputs setting one to be a synchronous by setting SynchronousInputID of the "unmatch" output to equal the InputId of the input, and for the "matched" output setting InputId=0. The PrimeOutput() method simply needs to assign the output buffer to a private variable. Then, in ProcessInput(), read the row, process it using the external API, and then add the row straight into the output buffer. If you would like to see more, here is the post that I placed on the forum that helped to solve my probelm http://forums.microsoft.com/MSDN/showpost.aspx?postid=295247&siteid=1&message_id=138175&notification_id=138175

2 Mar 2006

SSIS File handle leak in For Each Loop

I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time.

Update: I put this on the Microsoft forums and got a intresting answer back See Here;
There seems to be a memory leak with foreach loop which should be fixed in sp1.