One of the disadvantages with direct configuration (configuration location is hard-coded in the package) is, the location of the configuration file should be same as it was at the design time. For example, if you use the configuration file as D:\MyConfigFile\PackageConfiguration.dtsConfig at the design time, SSIS package expects the config file from the same path once it is deployed. One way to over some this issue is, disable the configuration from the package and compile, and assign the configuration file explicitly at run-time. Other way is, Indirect Configuration.

Indirect configuration can be simply implemented by setting the configuration location in the envoirnment variable. The configuration file can be deployed to any location. The SSIS package looks for the envoirnment variable for the location of the config file.

Applying same configuration file for multiple packages is possible. The header section of the configuration setting is totally ignored at the runtime (you can see the configuration section if you open the package with any text editor) and it tries to set all properties available in the config file to the package. You will see warnings if all properties cannot be applied to the package but can suppress all warnings by setting SuppressConfigurationWarnings true.

I am sure that you have experienced this issue. You might have seen that the content of the SP works fine (within the expected time-frame), but not the SP. There are many reasons for this, but I recently noticed this issue in one of the SPs which I examined. Let me share the issue and fix with you, it might be helpful WITH your work.

Assume that the below table contains 100000 (from the year 1753) records and you try to get some records by using the SP GetTestData.

CREATE TABLE TestTable (ID int IDENTITY(1,1) PRIMARY KEY, Date datetime, String varchar(1000))
GO
CREATE INDEX IX_TextTable ON TestTable (Date)
GO

CREATE PROC GetTestData @number int
AS
BEGIN

DECLARE @NewDate datetime
SET @NewDate = DATEADD(month, @number, getDate())
SELECT ID, String FROM TestTable WHERE Date > @NewDate

END
GO

Note that we can add "DATEADD(month, @number, getDate())" to the WHERE condition without any problem but presume that the formula is much more complex than this and cannot be added to the "WHERE" condition directly.

Now let's try to get some result-sets. If I need to get records for last 10 months, I will be executing the below query;

EXEC GetTestData -10

If you have enabled "Include Actual Execution Plan" and "I/O", you may notice that SQL Server has not used the index which we had added and uses more logical reads than we expected (14340).

You may try the query itself by hardcoding the value;
SELECT ID, String FROM TestTable WHERE Date > DATEADD(month, -10, getDate())

Now you will see that it has used the index and has read few pages (938) only, and has executed within a lesser time-frame. So, where have we made the mistake? Why didn't SQL Server use the index when the SP was executed? The reason is simple. When the SP is compiled, initially it has no value for @number parameter and because of this it generates the plan for 30% of the records in the TestTable, and in this case it generates the plan that tells SQL Server to do a clustered index scan rather than scan the index we added. So, it never uses the index unless we force.

To overcome this behaviour, or to force using the index, we can use the OPTIMIZE FOR option with the statement. Note that you have to know about the exact requirment for the SP before applying the OPTIMIZE FOR with a value. For example, it would be better to do a clustered scan for a large result set than an index scan.

Here is the new SP;
CREATE PROC GetTestData @number int
AS
BEGIN

DECLARE @NewDate datetime
SET @NewDate = DATEADD(month, @number, getDate())
SELECT ID, String FROM TestTable WHERE Date > @NewDate OPTION (OPTIMIZE FOR (@NewDate = '01/01/2007'))

END

Now we can see that;

EXEC GetTestData -10

is executed by using the index we added and with the time we expected and less page reads. So, if you face this issue, measure it, understand the requirement, and use OPTIMIZE FOR.

 

This error put me into a big trouble when I was doing the SSIS presentation. I couldnt figure out the reason for the problem but finally found out that it is a bug. Information about this has been published here and the patach can be downloaded from here.

I loved to make things with lego, and still interesting. Yesterday I helped my son to make this from his new lego-set.

 

The very first local SQL Server User Group Meeting held on 5th, Thursday. The first session was done by me, focusing on SSIS fundamental stuff and the second session that focused on BI was done by Gogula. My concentration on the session was interrupted frequently by my family (my mobile started ringing, ringing, ..) since my daughter was very sick. Unfortunately I had to leave the meeting early (I really missed the Gogula's presentation) and rushed her to the hospital. She is still in the hospital but getting better.

I really wanted to have chat with SQL Server enthusiasts, and talk about future sessions but had no luck. It seems that our SQL Server friends really want some advance stuff. Though we have already planned sessions for next three months, will be adjusting and publishing them soon.

Both sessions and sample applications will be published in the User Group Meeting forum in the SQL Server Universe web site.

If you are looking for SQL Server samples and Community projects from Microsoft, SQL Communities and SQL Server experts, CodePlex for SQL Server Samples is the site you have to look for. The content of the site seems very useful for SQL Server DBAs, engineers and whoever involve with SQL Server stuff. Though the most areas in the site are not filled with info, have a look, it will definitely give what we expecting.

The new searching functionality has been added for BOL; Scoped search that give you the rich result-set for the search you make. Go to the site http://search.live.com/macros/sql_server_user_educ... and see how useful it is.