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

Detecting High Memory consuming functions in .NET code

This post is just an pointer to a useful post by Shivprasad Koirala, which can be accessed from the link below
About the Tool
http://www.dotnetfunda.com/articles/article508-net-best-practice-no-1-detecting-high-memory-consuming-functions-in-net-code.aspx#
Download
CLR profiler is a tool which helps to detect how memory allocation happens in .NET code. CLR profiler tool is provided by Microsoft, you can download the same from http://www.microsoft.com/downloads/details.aspx?familyid=A362781C-3870-43BE-8926-862B40AA0CD0&displaylang=en .

Batch files - Count the command line argument or pass them to the executable

An interesting post that might help us http://stackoverflow.com/questions/1291941/batch-files-number-of-command-line-arguments, if the user is not user how many argument will be passed to the executable. This code might help
#1 Sample
set argC=0
for %%x in (%*) do Set /A argC+=1
echo %argC%

#2 Sample, more robust
@echo off
setlocal enableextensions enabledelayedexpansion
call :getargc argc %*
echo Count is %argc%
echo Args are %*
endlocal
goto :eof
:getargc
set getargc_v0=%1
set /a "%getargc_v0% = 0"
:getargc_l0
if not x%2x==xx (
shift
set /a "%getargc_v0% = %getargc_v0% + 1"
goto :getargc_l0
)
set getargc_v0=
goto :eof
Output
C:\> ArgList.cmd 1 2 3 4 5 6 7 8 9 10 11
Count is 11
Args are 1 2 3 4 5 6 7 8 9 10 11

Why this article is written: Windows Batch files usually support 0-9 arguments.

SSIS error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER when connecting to Oracle data source

Thanks to the useful post Jorge, here is what we might look for to resolve the issue:-
1. Check the Provider you are using Microsoft or Oracle
2. Check the links below in order to resolve the issue
Useful links
64-bit Considerations for Integration Services
http://msdn.microsoft.com/en-us/library/ms141766.aspx
SSIS error DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER when connecting to Oracle data source
http://blogs.msdn.com/jorgepc/archive/2008/02/12/ssis-error-dts-e-cannotacquireconnectionfromconnectionmanager-when-connecting-to-oracle-data-source.aspx
Oracle Drivers on an x64 Box for SSIS Development
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11

SQL SERVER 2005: CONCAT_NULL_YIELDS_NULL Demystified

What wondering for hours to figure out what might be the issue, here is what happened. Might help if someone is stuck with a similar issue.
For instance,
declare @TestString varchar(10)
--select @TestString = ''
select @TestString = COALESCE(@TestString + '/ ' + 'a', 'b', '')
select @TestString
OUTPUT:
(When SET CONCAT_NULL_YIELDS_NULL ON)
b
(When SET CONCAT_NULL_YIELDS_NULL OFF)
/a

so, if you are using something like this in your stored procedure, check the following option
SET CONCAT_NULL_YIELDS_NULL ON OFF

SSRS: Export report to the Excel 2003 file

Using the SQL Server 2005 SSRS you can format the reports and use the RDL file in your application to export it to an EXCEL 2003 file format
Note: Excel 2007 is not supported and
The deployment machine should have "Microsoft.ReportViewer.WinForms" in the GAC.

Sample Code:
//Namespace
using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Drawing.Imaging;
using System.Drawing.Printing;
using Microsoft.Reporting.WinForms;
//Namespace
// Function to get the datatable
private static DataTable LoadSalesData()
{
// code to get the Data Table
}
// Main code to render report to EXCEL 2003 file
static void Main(string[] args)
{
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;
ReportViewer reportViewer = new ReportViewer();
// Set Processing Mode
reportViewer.ProcessingMode = ProcessingMode.Local;
// Set RDL file
reportViewer.LocalReport.ReportPath = "MyReport.rdl"; //Keep the RDL file in the Bin Folder
// Supply a DataTable corresponding to each report data source
reportViewer.LocalReport.DataSources.Add(
new ReportDataSource("MyDataset", Program.LoadSalesData()));
// The Above Dataset name should be same as in the RDL file
// Process and render the report
reportViewer.RefreshReport();
// Render to Excel
byte[] bytes = reportViewer.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);
FileStream fs = new FileStream(@"c:\outputReport.xls", FileMode.Create);
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}

Deployment in the production Box
Deploying Reports and ReportViewer Controls
You can freely distribute reports and the ReportViewer controls as part of your application. Deployment requirements vary widely depending on the control type and whether your report is configured for local or remote processing. You can deploy locally processed and remotely processed reports in the same application.
Redistributing the ReportViewer Controls
The ReportViewer control redistributable is a self-extracting file named ReportViewer.exe that includes an .msi and other files. You can find ReportViewer.exe at the following location: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\ReportViewer\ReportViewer.exe.
When you run ReportViewer.exe, the following files are copied to the Global Assembly Cache folder on the deployment computer.
File
Microsoft.ReportViewer.Common
Microsoft.ReportViewer.ProcessingObjectModel
Microsoft.ReportViewer.WebForms
Microsoft.ReportViewer.WinForms
for more information visit http://msdn.microsoft.com/en-us/library/ms251723(VS.80).aspx

Issue:

While formatting data in the html, we can use CanGrow and CanShrink to adjust the Cell width and Height, but while exporting/rendering to Excel, all the cells in the Excel sheet having Text Wrap Enabled. There is no way to resolve this, except using Interop Services of Excel, but it requires Excel to be installed on the Production Box.

Resolution:

The issue occured when i select a single column and made CanGrow = False and CanShrink = True. If you select all the columns and set these values then the issue is resolved. ;-)