How to: Use the Values of Parent Variables in a Child Package

How to: Use the Values of Parent Variables in a Child Package
reference from MSDN: http://technet.microsoft.com/en-us/library/ms345179.aspx

To add a parent package configuration to a child package
1. If it is not already open, open the child package in Business Intelligence Development Studio.
2. Click anywhere on the design surface of the Control Flow tab.
3. On the SSIS menu, click Package Configurations.
4. In the Package Configuration Organizer dialog box, select Enable package configuration, and then click Add.
5. On the welcome page of the Package Configuration Wizard, click Next.
6. On the Select Configuration Type page, in the Configuration type list, select Parent package variable and do one of the following:
Select Specify configuration settings directly, and then in the Parent variable box, provide the name of the variable in the parent package to use in the configuration.
Important:
Variable names are case sensitive.
Select or Configuration location is stored in an environment variable, and then in the Environment variable list, select the environmentvariable that contains the name of the variable.
7. Click Next.
8. On the Select Target Property page, expand the Variable node, and expand the Properties node of the variable to configure, and then click the property to be set by the configuration.
9. Click Next.
10. On the Completing the Wizard page, optionally, modify the default name of the configuration and review the configuration information.
11. Click Finish to complete the wizard and return to the Package Configuration Organizer dialog box.
12. In the Package Configuration Organizer dialog box, the Configuration box lists the new configuration.
13. Click Close.

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