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!

Excel (and Access) Data Source in 64-bit environment

When you try to run a scheduled job (with SSIS package) that connects to Excel or Access file on 64-bit server you will get errors like following:

Excel Destination 1 1 [15]] Error: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "Excel Destination 1" (15) failed validation and returned error code 0xC020801C.

[Connection manager "Excel Connection Manager "] Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

This is all due to the fact that SQL Agents tries to run SSIS package using a 64-bit version of dtexec.exe. The utility will try to use 64-bit version of JET/ISAM driver to access Excel/Access files and fail since they do not exist.

Solution is to run 32-bit version of dtexec.exe. You can do that by manually creating the command that references the right utility. First, copy the dtexec parameters of the job step that you created (see picture):

Then replace it with a new Opertaing System (CmdExec) job step and assemble the command line from reference to the 32-bit dtutil (typically C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe) and copied parameters.

Cannot execute in 64-bit environment because the script is not pre-compiled

The task cannot execute in 64-bit environment because the script is not pre-compiled. Please turn on the option to pre-compile the script in the task editor."

This error is also result of .NET Framework incompatibility. Proper solution is to upgrade to SP2 or to install hotfix. However, if your IT does not want you to do that before thoroughly tested, you can do following:

• Make sure that each script task has PreCompile = True
• Open Script Task Editor, switch to Script tab and make sure that PrecompileScriptIntoBinaryCode = True
• Click on Design Script to open code editor (Visual Studio for Applications) and then choose it using File > Close and Return (VSA will recompile and store binary code in the package).
• Build project (and copy the package from Bin folder to target location)

couple of other gotcha's I have found, 2/28/2008

Reviewer: D (Washington DC, United States)

Also if there are any viewers or any other type of break point in the package remember to remove it also as it will cause it to fail as well. Also I found that if you are referencing 32 bit dll's in your script you need to set run64bitruntime to false.

SSIS and Vista (.NET Framework incompatibility)

If you have Vista as a development machine, you should upgrade to SP2 or upload fixes described in the following knowledgebase article. Apparently, there is a difference in version of .NET Framework which prevents Script task from working properly.

http://support.microsoft.com/default.aspx?scid=kb;en-us;931846

This problem may occur also if you have .NET Framework 2.0 hotfix later than build 2.0.50727.300 installed.

Acquire Connection Error in SSIS

I got this error in SSIS:

" [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DW.PPC2" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed."

I was not able to run the package, but I was still able to preview records in that OLEDB Data Source (in Visual Studio).

I was unable to find anything tangible in BOL, log or Google. Finally, I want to the server and realized that Named Pipes are disabled. I created an alias that forces connection to use TCP connection to the database server and my problem was solved.

Put Expressions in Variables

Instead of putting expressions in object properties, try putting them in variables. That will allow you to debug them (in Watch window) and to reuse them for other objects.

(Expressions are features of SSIS packages that allow you to assemble values of properties at run time of dtsx / SSIS package. They are replacment for Disconected Edit in DTS 2000. Expressions are set in Visual Studio when you start editing individual package. )

Long Validation of SSIS Packages in Visual Studio

One of the irritating things regarding creation of SSIS packages is that Visual Studio (or Business Intelligence Development Studio) can take couple of seconds (or even couple of minutes) to validate .dtsx packages against data sources (databases). This is annoying when you are opening a single package (from Solution Explorer), but it is really frustrating when it happens during opening of a Integration Services project that contains multiple packages. Visual Studio will validate each open package.

Solution is to close all opened package before closing Visual Studio.

Managing files in Script Tasks

Script Task in SSIS can be used to manage files on a server. I will give just simple examples here. Some of them can be performed (even simpler) using different methods like File System Task or batch files, but scripts are really useful when you have to do something more complex. Example of that would be when location of files are dynamic (typically different server in different environments).

Classes for managing files are in System.IO namespace. You could add that in the header of the class and then you do not have to reference that, but to make things obvious, I will repeat System.IO even when it's redundant.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim sFileA As String

'Read location of the file from connection string
sFileA = Dts.Connections("userLog").ConnectionString
Dts.Variables("userLog").Value = sFileA
'debug MsgBox(Dts.Variables("file_userLogToLoad").Value.ToString())

sFileB = "F:\Folder1\userlog1.txt"
sFileC = "F:\Folder2\userlog2.txt"

'check if file A exists
If System.IO.File.Exists(sFileA) Then
' check if file B exists
If System.IO.File.Exists(sFileB) Then
'Copy a file (A -> C)
System.IO.File.Copy(sFileA, sFileC)

'append C with B
Dim s As String
s = System.IO.File.ReadAllText(sFileB)
System.IO.File.AppendAllText(sFileC, s)

'' To Delete userlog file for next user log load
System.IO.File.Delete(sFileA)

''' To rename/Move a file
System.IO.File.Move(sFileC, sFileA)
End If
Dts.Variables("TruncateWorkTable").Value = 1
End If
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Copy file on network drive with(out) UNC path using Cmd file

SSIS has tasks for managing files. However, sometimes it is easier to perform some operations in a batch (.bat or .cmd) file.
Some of commands in it (like Copy) have roots in DOS, so they do not support more modern options like UNC paths (just drive pats).
The following code defines drive that points to UNC path. It is also an opportunity to specify login and password needed to access remote folder (otherwise everything will be executed in context of current user).

Rem Define Drive for target UNC path
net use L: \\Server.Domen\SharedPath /user:Domen\User Password

Rem Perform operation
copy L:\SubFolder\original.txt F:\Folder\SubFolder\Destination.txt

Rem Wait for process to finish
sleep 30

Rem Remove Drive
net use L: /delete

SSIS Indirect Configurations

One thing that is not clear in BOL and many web articles about Indirect Configurations is how to do it.

1. Create configuration directly (in a dtsConfig file on the local disk) using Package Configuration Organizer (SSIS > Package Configuration…).
2. Create environment variable that points to the file (you can use Set command in cmd.exe window)
3. Restart Visual Studio or you may not see the variable in VS. In some cases you may even need to re-login .
4. Open the Package.
5. Reopen Package Configuration Organizer and edit the configuration.
6. Change it to Configuration location is stored in an environment variable (see Figure) and choose Environment variable from the list. Then press Next, save everything…

If only we could change the folder in multiple environment variables in one quick change.

SSIS Convert Indirect Configuration to Direct Configuration

If you switch from Indirect to direct configuration of the package, you will lose dtsConfig file. The proper way to do is to drop Indirect configuration and then Add direct (you will be prompted to reuse existing file).

Setting Environment Variables

A quick way to set environment variables is to add/edit them in registry. They are in following node:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment

I don't think that you should use user environment variables.

HKEY_CURRENT_USER\Environment

If you need to change a set of variables, simply create a .reg file with content such as:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment]

"dtsConfig_GeoLog"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\GeoLog.dtsConfig"

"dtsConfig_DW-PPC2"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-PPC2.dtsConfig"

"dtsConfig_OLTP-PPC"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\OLTP-PPC.dtsConfig"

"dtsConfig_SMTP"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\SMTP.dtsConfig"

"dtsConfig_MailToFrom"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\MailToFromdtsConfig"

"dtsConfig_DW-Financials"="C:\\SSIS Package Working Folder\\SSISLegacy\\SSISLegacy\\DW-Financials.dtsConfig"

You can create this type of file by manually editing nodes in RegEdit and then by Exporting them.

Note: After you create these variables, they may not immediately become available to you. You may need to restart process (Visual Studio) or even re-login.

Simple way to set dtsConfig - SQL Agent step

There is a much simpler way to set location of configuration files then Indirect Configuration - through SQL Agent jobs:

Intro

This blog is dedicated to development and management of SSIS (or DTSX) packages in SQL Server 2008 and SQL Server 2005.