SQL Server 2010 vs. SQL Server 2008 R2

Microsoft initially planned to call next version SQL Server 2010. As they were working on it, they focused only on some aspects of it and due to the fact that most components didn't have significant changes decided to call it R2.l

That was a fair decision. I see only one problem with it. It will have a significant impact on adoption rate of SQL Server 2008.

Unfortunately, most shops waited until 2008 was released to upgrade their 2000 servers to 2005. Their rational for not upgrading to 2008 was that Service Pack 1 was not yet available. If Microsoft named next release 2010 as initially planned, everybody would hurry to deploy 2008. Unfortunately, most non-technical decision makers will not even notice R2. They will simply continue running their shops on 2005.

Technorati Tags: SQL Server 2008 R2,SQL Server 2008,SQL Server 2010,SQL Server 2005,SQL Server adoption rate

SSIS not cluster aware

Unlike Database Engine and Analysis Services, Integration Services is not cluster aware. If you need to patch (upgrade) it, you will have to do it manually on every node.

Windows Cluster does not provide for SQL Server

1. CPU scale out
2. redundant storage
3. load balancing

However:

1. Wait for SQL Server 2008 R2. Microsoft is utilizing technology they bought - Datallegro
2. set it on SAN
3. Wait for SQL Server 2008 R2. Microsoft is utilizing technology they bought - Datallegro

Current date in SSIS expressions

How to make a file with current date in SSIS? You can build a file name using an expression builder in SSIS. There is a function GetDate() that returns current time (not date) in form of DB_Timestemp SSIS data type. You can extract just date if you cast it using (DT_DBDate) function. You will also need to convert it to string and add static parts of file name to it:

"c:\\backup\\file"
+ (DT_DBSTR 10, 1252)(DT_DBDate)GetDate() + ".txt"

XML Notepad

Better way to view/edit DtsConfig files: XML Notepad from Microsoft.

NullReferenceException - PrimeOutput Error

I got an error in SSIS package that is reading data from DB2/AS400 into SQL Server 2005 x64. All rows from the source table were moved to the target table, but DataReader Source still failed:

[DataReader Source VALNREQ [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

[DTS.Pipeline] Error: The PrimeOutput method on component "DataReader Source F57005" (1) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

After a while I realized that a column has been added to the source table. I refreshed the DataReader Source and directed new column to target and finally everything was working.

It would be short sighted to blame Microsoft for this error. I was using select * from table in DataReader Source. I changed it to the current a list of columns and I hope that my package will not fail if someone adds column to the source.

SSIS Send Mail – Mailbox Unavailable

Scenario:

· new development SQL Server 2005 server;

· project to upgrade existing DTS packages to SSIS;

· to notify users of failure, we wanted to use Send Mail task

· We got the following error:

Mailbox unavailable. The server response was: 5.7.1 Unable to relay for myemail@mydomain.com).

This was a new server and network administrators assured me that I am using right login and server (and that I do not need to specify username and password for SMTP server).

To take SSIS out of equation, I tried to configure Database Mail and send test email, but I got the same result. After a while I realized that we should use Windows Authentication using Database Engine Service Credentials instead of Anonymous Authentication.

I came back to my SSIS package and set SMTP connection to use Windows Authentication and then everything was working.

I assume that same error can be encountered when you do not have the right name of the account, name of the server…

Security Architecture of SSIS Packages

One thing that I am thinking about lately is how to make sure that package running in Development or Test environment does not change something in Production? Such problem may occur when:

1. a DBA forgets to update the configuration file
2. test or development is done in mixed environment (for example,  test DW servers are loaded from Production OLTP servers)
3. same connection (and login) is used for both reading and writing…

One way to solve that would be to use different logins in these environments. SQL Server logins (now that they are more robust in (SQL Server 2005) comes to mind as a potential solution.

Another solution is to give developers two sets of Windows accounts: regular login for everyday development work and a special login for firefights in Production  environment. That way a developer will not be able to mix up the privileges and accidentally change something in production.

SSIS Package Template in Visual Studio

If you put a package template in the following folder:

%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

It will be available in Add > New Item:

Just make sure that you (re)generate new ID for each package created this way.

New Package ID

After a package is created using a package template, you should change its ID. If you forget to do that, SSIS logging will use same id for both original and the copy and you will not be able to differentiate in the log between results of these two packages.

• Open the package in VS. Make sure that you are in Control Flow.
• Click on ID property and choose Get New ID from the menu.

dtsConfig Sharing - Suppress Configuration Warnings

Often I have one dtsConfig file for my SQL connections for a project.   Unfortunately, the problem starts when you add a connection to one of the packages that will not be shared across. The other packages that use this same dtsConfig file will attempt to load it and throw an error since they don't contain the new connection.

One solution is to use a separate dtsConfig file for each DB connection, and then each package will selectively use only the ones it needs.

Alternate solution is to set SupressConfigurationWarnings property to True.

Another solution., 12/9/2009

Reviewer: Pelle (Halmstad, Sweden)

I only have one config file (ServerVariables.dtsconfig)
that all my packages consumes. The config file sets local variables that i use in expressions for the connectionstring (ServerName). All packages must have local variables for every db connection but it is up to me if i want to use them in the package.

Good Point, 11/2/2009

Reviewer: SSIS Developer (JerseyCity, United States)

Good Point!