SSIS: Working with Parameters in the Execute SQL Task (ADO.NET)

Using Parameters with Stored Procedures

reference: MSDN

Using Parameters with ADO.NET and ADO Connection ManagersADO.NET and ADO connection managers have specific requirements for SQL commands that use parameters:
ADO.NET connection managers require that the SQL command use parameter names as parameter markers. This means that variables can be mapped directly to parameters. For example, the variable @varName is mapped to the parameter named @parName and provides a value to the parameter @parName.

ADO.NET Set IsQueryStoreProcedure is set to True.

To provide values to parameters, variables are mapped to parameter names. Then, the Execute SQL task uses the ordinal value of the parameter name in the parameter list to load values from variables to parameters.

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