SSIS: How to fix 'Can't get lock on variable or deadlock occured' error when you are trying to handle exception using Script Component

Issue:

If you've a Script Component in the Control Flow, which is writing to LogFile (DtsVariable), and an exception occurs, and if you Exception Handling Component also using the same LogFile (DtsVariable) to log error to the file then you enter a deadlock scenario. It happened because the Script component was still holding the lock but flow jumped to the Exception Handler. All that needs to be done was to un lock variable before we jump to the Exception Handler Block.

Resolution:

Try

...

Catch ex As Exception
Logfile.WriteLine("Exception Occured: " + ex.Message)
Dts.Variables.Unlock()
Dts.TaskResult = Dts.Results.Failure
Throw New Exception(ex.Message)
End Try

Additional Reading:

Reusable Functions for Reading and Writing Variables in SSIS Script Tasks

http://www.developerdotstar.com/community/node/512

Partitioned Tables and Indexes in SQL Server 2005

Partitioned Tables and Indexes in SQL Server 2005
reference: http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx

This article is simple abstract of what has to be done. If you've a scenario wherein you need to partition a large table Orders based on for instance order date (datetime datatype). We can either partitioned the table for each month, Quarter or Half yearly depending on what is expected requirement.
Step1
-- add filegroup to the database
-- first filegroup
ALTER DATABASE TestPart ADD FILEGROUP [2007HY1]
-- second filegroup
ALTER DATABASE TestPart ADD FILEGROUP [2007HY2]

Step 2
-- add files to the filegroup
-- add first file to the file group
ALTER DATABASE TestPart
ADD FILE
(NAME = N'2007HY1',
FILENAME = N'C:\TestPart\2007HY1.ndf',
SIZE = 5 MB,
FILEGROWTH = 20%)
TO FILEGROUP [2007HY1]
-- add second file to the file group
ALTER DATABASE TestPart
ADD FILE
(NAME = N'2007HY2',
FILENAME = N'C:\TestPart\2007HY2.ndf',
SIZE = 5 MB,
FILEGROWTH = 20%)
TO FILEGROUP [2007HY2]
Step 3
-- Partition Function
CREATE PARTITION FUNCTION OrderDateRangePFN_HY (datetime)
AS
RANGE LEFT FOR VALUES ('20070630 23:59:59:997',
'20071231 23:59:59:997')

Step 4
-- Partition Scheme
CREATE PARTITION SCHEME OrderDatePScheme_HY
AS
PARTITION OrderDateRangePFN_HY
TO ([2007HY1], [2007HY2], [PRIMARY])

Step 5
-- Create Partitioned Table
CREATE TABLE OrderRange_HY
(
OrderID int NOT NULL,
OrderDate datetime NOT NULL
)
ON OrderDatePScheme_HY (OrderDate)
GO

-- Converting Unpartitioned table to Partitioned Table
CREATE INDEX OrderPart_INX
ON OrderRange_HY_Existing (OrderDate) -- this is an existing table
ON OrderDatePScheme_HY (OrderDate)
GO

Using Partitions in a Microsoft SQL Server 2000 Data Warehouse: http://msdn.microsoft.com/en-us/library/aa902650(SQL.80).aspx