Thursday, July 06, 2006

SSIS Parameter Mapping problem

For our archiving project we've decided to use Integration Services since we are quite impressed with the power and flexibility of the engine.The problem with SSIS however is that is quite new so you don't know all the little gotcha's like you do in DTS after 5 years :-)

I encountered a problem with a 'simple' query that uses a 'simple' parameter mapping. Just plain old T-SQL without any real rocket science.

DELETE sl FROM dbo.tbl_subsystem_transaction_log sl WITH (TABLOCKX)
INNER JOIN dbo.tbl_subsystem_transaction s WITH (TABLOCKX) ON s.SubSystemTransactionID = sl.SubSystemTransactionID
WHERE s.CreationDateTime < ?

This however resulted in Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DELETE sl FROM tbl_subsystem_transaction_log sl INNER JOIN tbl_subsystem_transaction s ON s.SubSystemTransactionID = sl.SubSystemTransactionID WHERE s.CreationDateTime < ?" failed with the following error: "Invalid object name 'sl'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I used a fixed date it would execute fine but as soon as the "?" comes in... failure. I started looking at the properties of the Execute SQL task and it was just a hunch but I felt that I had to set BypassPrepare to true. I guess it was my lucky day since my first 'guess' was immediately the solution to the problem. Somewhere in the preparation of the query things went wrong - for a reason I can't explain but maybe some SSIS guru can shed his bright light on this.

Wednesday, July 05, 2006

Best Practice Analyzer for ASP.NET

A new tool is provided by Microsoft to make sure you are doing things right in ASP.NET: the Best Practice Analyzer for ASP.NET

From the Microsoft website:

The Best Practice Analyzer ASP.NET (alpha release) is a tool that scans the
configuration of an ASP.NET 2.0 application. The tool can scan against three
mainline scenarios (hosted environment, production environment, or development
environment) and identify problematic configuration settings in the
machine.config or web.config files associated with your ASP.NET application.
This is an alpha release intended to gain feedback on the tool and the
configuration rules included with it.

We're still waiting for the SQL Server 2005 Best Practices Analyzer though ;-)