Partitions & Identity Columns

Question
When I switch the data to the main table partition, it is ignoring the Identity specification column on and duplicating the values in the Identity column.

I did a search on Books on Line and all I could see is Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC_CHECKIDENT to check the identity values of your tables and correct the values if you want. But it doesn't tell me how to use DBCC_CHECKIDENT.

Answer
When you are inserting data into your empty table, reseed that table before you start with the max+1 value from the main table, a quick query should work that out.

Insert your data and then push it into your main table, no issues.

Scenario
My table partition is based on the date. I've created my Temp tables dynamically based on the date. And before inserting the records into Temp table I've retrieved the (max + 1) value of the Identity column from the main table and reseeded the Identity of the Temp table with DBCC CHECKIDENT

Thanks
Sutha

Kimball Group Webcasts - MSEevents

Following their book on SQL Server 2005, Kimball group is going to conduct 4 TechNet webcasts. It should be well worth listening material. You can rigister for those webcasts using the links below:

Microsoft Business Intelligence (BI) Using the Kimball Method
Designing a Scalable Data Warehouse / Business Intelligence (DW/BI) System
Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse
Getting Started with Data Mining


Thanks
Sutha

Shredding Recordset Object In ForEach Loop - While Using Checkpoint

I think this got to be one of the annoying bugs within SSIS.

I have a SQL Task which returns the result set to a variable called "Pkg_Obj_ResultRet".

Then using Foreach Loop container I shredd the result set. I have a Foreach ADO Enumerator and the Enumeration Mode is set to Rows in the first table. Shredded results are mapped to 2 variables called Pkg_Str_SSISStreamName and Pkg_Str_SSISVarNameControlFile.

Inside Foreach Loop Container I have "Execute Package Task" which execute package using these variables. Here comes the fun.

The iteration works fine first time, runs the second iteration successfully and then it try to run second iteration in an infinite loop.

This happens when you have SaveCheckPoints option set to True. If you want to use checkpoint, you can't get this to work unless you set the Foreach Loop TransactionOption = Required.

I think this is a bug and I have raised it with Microsoft.

Thanks
Sutha

Cumulative hotfix package (build 2153) for SQL Server 2005

As we all know there were we some issues with SP1. Microsoft released hotfix package, which can be downloaded from here.

Thanks
Sutha

Getting SSIS Packages To Run On A 64 Bit Box

We had some serious issues getting the SSIS packages to run on a 64 Bit platform which connects to Oracle database.

Mark Hill managed to get this bloddy thing to work. He wrote a simple document explaining what he did. I thought I would share it with all of you.

-----------------------------------------------------------------------------------
When we first approached this, I would have thought it should have been as easy as installing the 64 bit drivers from the Oracle CD and then copying a package across and running it.

This approach did not work, no matter which driver was selected at design time (Oracle Client, Dot Net Provider etc) we could not establish a connection.

When opening up the package on the server with the BIDS environment we found that we were getting bad image messages or init failures for the driver from the design dialog.

Going back to basics, I thought this through a little.

If BIDS is a 32 bit process (which it is), it cannot load the 64 bit drivers, therefore that would go someway to explaining the bad image error message.

Solution?! Let’s try and install the 32 bit Oracle client on the 64 bit box.

Install worked, however the 32bit drivers also failed to load under BIDS.

By this time I was pulling my hair out for real.

Next we developed a package on the 32bit laptop, was sure to choose the Oracle driver, copy it across and run it. No, the 64 bit run time of SSIS cannot run this package.

Back to the drawing board (web search)

Three pieces of interesting information (there is actually four)

1) There are two versions of SSIS installed on a 64 bit by default, one in x86 programs and the other in programs
2) The 32 bit Oracle drivers do not like x86 in their path name (may explain the odd 32 bit driver errors under BIDS)
3) A package can be told which subsystem to use, package properties, 32 bit or 64 bit.
4) If you use scripting code, ensure that the flag is set in the properties to allow dynamic recompile, or else you will get an error message loading the vbscript code on 64 bit.

Reality

Well there are two versions of SSIS, which you can run from the command line prompt, dtsexec.exe, if you use the /f option then you will be able to specify a filename for it to run.

The dynamic code flag, sometimes does not work, if you have this problem, open the package on the 64bit box and then resave it.

Eventually after all of this we were able to copy across a package and get it to work accessing an Oracle box.

All in all a bloody nightmare.

-------------------------------------------------------------------------------------

Thanks Mark

Sutha

First BI User Group Session

BI User group has been set up by Chris Webb and Tony Rogerson. The first meeting is set for 22/06/2006. Register here if you wish to attend. Look forward to see you there.

Thanks
Sutha

Installing SQL2005 SP1 - checking for locked files via Windows Installer file sqlrun_sql.msp

Please note that you have to shutdown all the services except SQL Server service, before you start SP1 installation.

When I tried to install SP1 yesterday I came across a small problem. I had all SQL related services shutdown, except SQL Server service (It is required to run when SP1 installation starts - Installation process will shut SQL Server service later on during installation). I came across this problem. During Database Services, installation process hung stating the following message.

checking for locked files via Windows Installer file sqlrun_sql.msp

I tried few things and couldn't get it to work.

Eventually I moved all the user created database files (MDF and LDF) to a different directory from SQL Data directory (in my case - C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to a temp directory.

Then I re-installed SP1 and it went through without any problems.

Thanks
Sutha