I had several posts planned for this past week, however Christmas Eve I fell ill from food poisoning and all that was planned went out the window. It's been a very slow week in recovering, but I'll be back on track soon and have my planned items posted soon.  I hope everyone had a good holiday season and I personally am looking forward to a great year in 2008 -- so best of luck to everyone out there for the coming year!

For those of you who like podcasts, here is something published by SQLServercentral.com: http://sqlservercentral.podshow.com/

Expression constraints can be very handy in SSIS. Building upon an earlier post,

Extracting SharePoint Data using SSIS, I will show how I extended the package described in that post order to get around some issues we were having in working with WSS 2.0 lists and query timeouts.

In this situation, I do not have access to make any changes to the WSS 2.0 farm; here are two links that talk about configuring WSS 2.0 web services to potentially avoid the same timeout issue I face in this particular situation – essentially, it involves similar steps you would follow in order to enable large file support in WSS/SharePoint:

-         http://office.microsoft.com/en-us/winsharepointadmin/HA011607881033.aspx

-         http://blog.gavin-adams.com/2007/06/28/configuring-large-file-support

In order to avoid timeouts on a very large WSS 2.0 list, I had the script component dynamically build the CAML query that gets used in the Query element in the web method call. This essentially batches my information download from the list so that I do not face a timeout. 

What I use the precedence constraint to do is determine whether the package goes on to the next step.  In the picture below, I have my Script Task that is responsible for generating the XML file from the web service query – since not all queries in the query set will generate data, I want to keep the process from proceeding to the next step if the result set from the web service is null:

I have a variable in the scope of the package which is a Boolean for whether or not I retrieved a result set from the web service for the particular query. The section of code below checks the Item Count in the result set and appropriately sets my Boolean value:

  Dim r As System.Xml.XmlNoder = node.SelectSingleNode("//*[local-name()='data']") //node is the result set from the web service query

 Dim c As Int32c = Int32.Parse(r.Attributes("ItemCount").Value.ToString()) 

So if we have a positive count, then Dts.Variables("FileCreated").Value = True, which allows this operation to work on my precedence constraint:

This way the package only cleans and logs the resulting file if we had a positive return on the number of records from the web service call -- simple example on one way to use a precedence constraint in SSIS.

I'm not going to get into what platform is better for virtualization, but rather talk about a common issue for those of us using virtual machines to test and develop. I personally run Virtual Server 2005 R2 on my laptop, mostly because it's free, but also because it is a decent platform for what I do. 

Running out of virtual hard disk space can be a pain, and I dislike attaching more virtual drives to a particular machine because I do not want the complexity -- the virtual machines are for testing and developing, not production. So what to do when you have accidentally set the virtual hard disk size too small for your needs? Here is where this awesome tool I found comes in handy: http://vmtoolkit.com/files/folders/converters/entry87.aspx

It's called VhdResizer; it simply makes a bit for bit copy of the image you have and resets the hard drive size to your new requirement. Pretty simple.

After installing, launch the app -- it straight away goes to a file dialogue box. Be sure to have stopped the virtual machine you are resizing:

Here is the main action screen. Choose a new name for the file, new size and go!

It will copy and create your new file:

Detach the old image from your VM and attach the newly created one. When you log back in, you will need to configure the disk, but the extra space is now there:

If you are relatively new to using CAML (which if you are going to be customizing SharePoint, you most likely will), there is a great tool out there to help learn. Patrick Tisseghem has a blog at http://www.u2u.info where he posts a lot of information centered around SharePoint.

For his entry on the U2U CAML Query builder: http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1252

This is a great tool for assisting in building your queries. Just note that if you are going to use the output of this tool in C# or VB.Net classes, you will need to use the unique name that SharePoint assigns to the column, which is a modified GUID.  To get the details on your columns in a List, use the GetList method in the List web service. Your resulting set will show the display name that you are familiar with but also something similar to: Name="xd__x007b_F18C5698_x002d_C5B6_x002d_4D60_x002d_B42C_x002d_61D89A229F82_x007d_, which is the name you will need to reference in your code.

Here is a screenshot of the application:

If you are using a Crystal report on a .NET 2.0 web site via the CrystalReportViewer, you may get some odd behavior unless you initialize the component properly.  

I have a Crystal Report on a web page that gets automatically loaded the first visit with date variables: 

DateTime _s = DateTime.Now.Subtract(TimeSpan.FromDays(28));

DateTime _e = DateTime.Now;LoadCrystalReport (_s, _e); 

Here is the method that gets called: 

private void LoadCrystalReport(DateTime sT, DateTime eT)   

{        

try       

{           

ReportDocument rd = new ReportDocument();            

rd.Load(Server.MapPath(@"reports\NewCrystalReport.rpt"));            

rd.SetParameterValue(0, sT);           

rd.SetParameterValue(1, eT);            

CrystalWebPartViewer.ReportSource = rd;       

}       

catch (Exception _ex)       

{             //do something here         } 

    }

 If I place the first block of code in Page_Load, then the CrystalReportViewer menu buttons (Refresh, Export, Print, and the paging buttons) throw an object reference error or do not work. For the CrystalReportViewer menu buttons to respond, it needs to be initialized during Page_Init.  

Why? Page_Init is where events for server controls get wired. If you notice in a C# web project, all pages have the AutoEventWireup property set to true. What this means is that the CLR can now use the ObjectName_EventName standard to find and wire up all of your control events at run time. VB.Net projects have this set to false due to the Handles keyword used in a method declaration. By using the AutoEventWireup, all of the Designer generated code you used to see in your .NET 1.x pages, similar to: This.Button1.Click += new System.EventHandler(this.button1_Click); is now generated by the CLR at run time. 

By initializing the CrystalReportViewer during Page_Init, you are giving the CLR the opportunity to now wire up the controls that will be built by the Crystal dll. 

Note: This is not an endorsement of Crystal as a reporting tool, rather an explanation of an issue experienced while working on a client project.