Friday, May 29, 2009

SQL Server 2008-32 Bit Runtime...

We have a decent size of SSIS packages in Sql Server 2008 where in i import data from Excel Data sources. These SSIS packages run on a SQL Server 2008 box which has a 64 Bit installation of SQL Server. Since Excel is a 32 bit application, in order for the SSIS packages to work I had to use the command line option to run the SSIS packages where in i can invoke the 32 bit SSIS runtime so that it would work with the Excel Data sources. In Sql server 2008 one can use the SQL Server Integration Services option and set the 32 bit runtime check box. This option is available in the Execution Options tab. This way one can move away from the command line option. Here is a snapshot of setting:

Wednesday, May 20, 2009

New Features in SQL Server 2008...

There are Several New features in SQL Server 2008. In one of the instance I had upgraded 2005 SQL Database to SQL 2008. Then later i found out that i had to recreate the database in 2005 again. At this point i did not have SQL 2005 database backup, I was stuck for a while. Then on exploring the various tasks i found the following task which helped do my job. In the SSMS 2008, right click on the database, then click on Tasks, then click on Generate Scripts.

This will launch the Script Wizard, Choose Next, In the next screen choose the database, then choose the Choose Script Options. In this there is a option for Script for SQL Server Version, in this choose SQL Server 2005. This will generate the scripts compatible with SQL Server 2005. This would enable to create all the objects required for a SQL Server 2005 database.

There is another neat feature in this screen which the Script Data option, in case the sql server 2005 database needs to populated with Data, this can be set to true. This would enable to load a sql server 2005 database. These features came in handy when i had to go back to a SQL Server 2005 database.

Wednesday, May 13, 2009

Upgrade to SQL Server 2008...

Currently I am involved in the upgrade of Sql Server 2005 databases to Sql Server 2008. I did an inplace upgrade on the SQL Server 2005 Database to 2008, the whole process was seamless. Prior to upgrading i stopped replication and dropped all publications and subscriptions. During this upgrade process i had to upgrade SSIS packages in 2005 to 2008. In SSIS 2008 there is a upgrade wizard which helps one to migrate 2005 packages to 2008.
When I opened up BIDS(Visual Stusio 2008) and selected my SSIS 2005 Solution, the upgrade wizard popped up.

When I clicked the Next Button, it came up with the list of packages which needed to migrated. The window had three columns Existing Package names, upgrade package name and passowrd. In case the upgrade package name needs to be changed it can be changed here.

In the next screen is where there is a couple of important options. One of the things that has changed between ssis 2005 ans ssis 2008 is the connection strings to use new provider names. There is a checkbox which provides the option to do the conversion by the wizard itself. Having this box checked is highly recommended, this would make the upgrade process a lot more easier. There is a option to validate pacakges after upgrade and one for backing up original packages.

In the next screen one gets a summary of what needs to be done, once you hit finish the upgrade process starts and provided warning messages/errors once it is complete.

One thing i noticed after the upgrade was if i were using configuration files from SSIS 2005 in SSIS 2008, in some cases i got a XML badly formed error when i executed the package in SSIS 2008. In such cases i recreated the XML config file in SSIS 2008 environment.

Monday, May 4, 2009

SSIS 2008...

One of the challenges i was facing while developing SSIS packages was how to move around the packages through the different environments. There is a enable configuration option in the SSIS designer /BIDS. There are different kinds of configurations avaialble. They are:

XML file Configuration
Parent Package Variable
Environment Variable
Registry Entry
SQL Server

The options I choose were XML configuration and the SQL Server method. In the XML configuration option, In the Configuration wizard, Once has to choose the location for the XML File, Once that is done in the next screen, the properties for the different connection objects can be saved. This would typically contain the connection strings for different databases. In the final screen one can see all the different properties that are going to save in the XML file. Once this is complete the XML file will contain the important properties needed for the package. When the package is executed it uses the XML file to set the different connections. While moving across environments one has to create am XML file for QA and Production with the connection strings modified accordingly.

There are various other blogs across the web which describe the SQL Server method of configuring, In this case the configurations are stored in a table within a sql server database.