Are You Following SQL Erudition yet?!

Like what you read here at SQL Erudition?! Why not follow the blog to get an email notification for new posts?

WordPress users can simply click on the Follow button. Not a WordPress user?! No issues! Follow via email address in the Follow|Subscribe field and you are done!

What is the RetainSameConnection Property of OLEDB Connection in SSIS?

I recently wrote about How to Use Temp Table in SSIS. One of the requirements to successfully reuse a temporary table across multiple tasks in SSIS is to set the RetainSameConnection property of the OLEDB Connection to TRUE. In this post, I will discuss the property and also use a Profiler Trace to find out the behavior of this property.

The Property

RetainSameConnection is a property of an OLEDB Connection Manager. The default value of this property is FALSE. This default value makes SSIS execution engine open a new OLEDB connection for each task and close that connection when the task is complete. I believe the idea behind this is to not block a connection to a server unnecessarily and release it until it is needed again. And it makes sense too, because some packages can run for an extended duration and may not need to be connected to an OLEDB server all the time, for example while parsing text files, sending mails, ETL operations not involving the OLEDB server in question etc. Releasing connections unless really required can be certainly helpful on busy servers because SQL Server needs some memory for each open connection. On the other hand, some scenarios require a persistent connection e.g. temporary table reuse across multiple tasks. We can set the property value to TRUE and then it will open just one OLEDB connection with a server and keep it alive till the end of the package execution. The property can be set via the Properties window for the OLEDB Connection Manager.

aalamrangi.wordpress.com-SSISRetainSameConnection2

The Temporary Table Scenario

Local temporary tables (with a # in front of their name) in SQL Server are scoped to a session. SQL Server drops them when the session is closed. This means, local temporary tables created in one session are not available in another session. In SSIS, with the RetainSameConnection set to FALSE (the default), a new session is opened for each task. Therefore, temporary tables created by a task are not available to another task.

Demo

I have a demo package with two Execute SQL Tasks and one OLEDB Connection Manager. The Execute SQL Tasks have a simple SELECT statement and they both use the same connection manager.

aalamrangi.wordpress.com-SSISRetainSameConnection1

I have a Profiler Trace to monitor the number of connections created by the SSIS package.

The first execution of the package is with the RetainSameConnection set to the default value of FALSE. The trace captures two pairs of login/logout events, one for each task. The second execution is with the property value set to TRUE. This time the trace captures only one pair of login/logout events.

aalamrangi.wordpress.com-SSISRetainSameConnection3

Conclusion

In most cases, the default value of RetainSameConnection=FALSE will be suitable. A developer should make a decision to enable it when the package tasks really need a persistent connection. In addition to the temporary table reuse, a TRUE value for this property can also be useful in managing transactions and reducing the number of recurring connection requests to a server.

How to Have Standard Logging in SSIS and Avoid Traps

As the number of developers and/or packages increase in a team, it becomes difficult to ensure that everyone is logging a common minimum number of events in the packages. The common minimum could be necessary for consistency in reports and general analysis. Documentation, checklists and trainings are helpful but the easiest way to ensure a common logging implementation would be a logging template.

I am assuming that you are already familiar with the concept of event logging in SSIS to monitor execution status. This post is not going to be a beginners level introduction to logging. I will rather discuss options to have a minimum standard logging of events across packages and teams with minimal effort. I’ll also mention some traps to avoid.

I am using a demo package with two Data Flow Tasks and a Execute SQL Task. I have enabled logging for the first few events at the package level for the sake of demonstration. The logging configuration options for the package node are shown in the first image.

Image 1 -  Logging configuration window for the package node

Image 1 – Logging configuration window for the package node

The logging options at the child container node Data Flow Task 1 appears as in the second image. The configuration for other Data Flow and the Execute SQL Task look the same.

Image 2 - Logging configuration window at the child container node

Image 2 – Logging configuration window at the child container node

The check marks for the tasks are grayed out which means they are inheriting the logging options from their parent, i.e. the package. To disable logging for a task, remove its check mark in the left tree view window. Logging can also be disabled by going to the Control Flow canvas and changing the LoggingMode property of the task to Disabled.

The Trick

Now look at the bottom of the images again. Notice the Load… and Save… buttons? They do exactly what they say. You can set your logging options and save them as an XML template. Later, this XML template can be loaded into other packages to enable the same logging options.

The XML template file has nodes for each event. For example, the logging options for OnError event are saved like this –


-<EventsFilter Name="OnError">

-<Filter>;

<Computer>true</Computer>

<Operator>true</Operator>

<SourceName>true</SourceName>

<SourceID>true</SourceID>

<ExecutionID>true</ExecutionID>

<MessageText>true</MessageText>

<DataBytes>true</DataBytes>

</Filter>

</EventsFilter>

Notice that the XML just mentions the event name, not the name of any task. This means that when the template file is loaded, this logging option will be set for any task where the event is applicable. More on this later.

The Traps

The OnError event is a generic event applicable to all tasks. Lets talk about events that are specific to tasks. For example, the BufferSizeTuning event is applicable just to the Data Flow Tasks, not Execute SQL Tasks.

When I proceed to set logging for BufferSizeTuning event, I have to set it individually in the Data Flow Task tree node. Notice the message at the bottom of the second image that says “To enable unique logging options for this container, enable logging for it in the tree view.”. This message is important in the context of saving and loading a template file too. When I save a template file, the logging options of just that tree view node are saved. For example, the BufferSizeTuning event will be saved in the template only if I am at the Data Flow task in the tree view. It will not be saved if I am at the Package or the Execute SQL task in the tree view.

The reverse is also true. When I load a template, its logging options are applied to just that node which I select in the tree view. For example, if I load a template at the Data Flow Task 1, the options will not be applied to the Data Flow Task 2 or the Execute SQL Task. If the template has an event that is not applicable to the task then that event’s settings will be ignored. For example, while loading a template file to an Execute SQL Task, even if the template has BufferSizeTuning event logging options, it will just load options for the events relevant to the Execute SQL Task. The BufferSizeTuning event will be ignored because this event is only meant for the tasks of Data Flow type. The fact that non-relevant options are ignored can be helpful for us to consolidate all logging options in a single template file.

Conclusion

A package level Save and Load of a logging template is straight forward. But if you need to have logging for events that are specific to a task type, then consider creating a logging template for each type of task. Also, if your logging configuration requires anything else than a package level settings, remember to load the template for each task in the tree view.

Number of Template Files How Pros and Cons
Individual File per Task Create one template file for each type of task. The file will have events applicable to that task. Pros –
Easier to know what type of tasks have a template and which ones do not.

Cons –
More files to manage.

Single File for All Tasks Create a template file for each task. Then copy all event options in a single XML file. Pros –
One file is easier to manage.

Cons –
Not obvious which tasks are include. Need to put in comments in the XML file.

How to Use Temp Table in SSIS

aalamrangi.wordpress.com-TempTableInSSIS-9

Using a temporary table in SSIS, especially in a Data Flow Task, could be challenging. SSIS tries to validate tables and their column metadata. As the Temp table does not exist at the design time, SSIS cannot validate its metadata and throws an error. I will present a pretty straight forward solution here to trick SSIS into believing that the Temp table actually exists and proceed as normal.

To begin with, I will demonstrate that a Temp table can be referenced across two tasks. Add two Execute SQL Tasks in your package. Both of them use the same OLEDB connection. The first task creates a Temp table and inserts one row into it. The second task tries to insert one more row in the Temp table.

aalamrangi.wordpress.com-TempTableInSSIS-1

TSQL script in the first task –

/* Create a LOCAL temp table*/
IF
(
Object_id('[tempdb].[dbo].[#LocalTable]')
IS NOT NULL
)
DROP TABLE
[tempdb].[dbo].[#LocalTable]
GO

CREATE TABLE [#LocalTable]
(
id INT IDENTITY,
label VARCHAR(128)
);
GO

/* Insert one row */
INSERT INTO [#LocalTable]
(label)
VALUES ('First row');
GO

TSQL script in the second task –

/* Insert one row */
INSERT INTO [#LocalTable]
(label)
VALUES ('Second row');
GO

When executed, the SSIS package gives the following error because the second task cannot see the Temp table created in the first task. Local Temp tables are specific to a connection. When SSIS switches from one task to another, it resets the connection so the Local Temp table is also dropped.

Error: 0xC002F210 at ESQLT-InsertSecondRow
, Execute SQL Task: Executing the query
&quot;/* Insert second row */
INSERT INTO [#LocalTable]...&quot;
failed with the following error:
&quot;Invalid object name '#LocalTable'.&quot;.
Possible failure reasons:
Problems with the query
, &quot;ResultSet&quot; property not set correctly
, parameters not set correctly
, or connection not established correctly.
Task failed: ESQLT-InsertSecondRow
Warning: 0x80019002 at SSIS-DemoTempTable:
SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.
The Execution method succeeded, but the number
of errors raised (1) reached the maximum
allowed (1); resulting in failure. This occurs
when the number of errors reaches the number
specified in MaximumErrorCount. Change the
MaximumErrorCount or fix the errors.
SSIS package &quot;SSIS-DemoTempTable.dtsx&quot;
finished: Failure.

The fix is pretty simple. Right-click on the OLEDB connection manager and go to the Properties window. Change the RetainSameConnection property to True. This will force the connection manager to keep the same connection open.

aalamrangi.wordpress.com-TempTableInSSIS-2

This fixes the error and the package executes successfully.

Now let me demonstrate that a Temp table can be used in a Data Flow Task.

Add a Data Flow Task to the package.

aalamrangi.wordpress.com-TempTableInSSIS-3

In the Data Flow task, add an OLEDB source that will use the same OLEDB connection as used by the Execute SQL Tasks earlier. In the OLEDB Source Editor window, there is no way to find our Local Temp table in the list so close the Editor window.

Open a SSMS query window and connect to the SQL Server used in the OLEDB connection. Now create a Global Temp table with the same column definition. You can just copy the CREATE TABLE script and add one more # symbol to the table name.

The Global Temp table is just a development workaround for the restriction imposed by the volatility of the Local Temp table. You can even use an actual physical table instead of the Global Temp table. We will switch to the Local Temp table in the end of this post and then the Global Temp table (or the actual physical table) can be dropped.

Script –

/* Create a GLOBAL temp table
with the same schema as the
earlier LOCAL temp table.
Note the ## in the table name */

CREATE TABLE [##LocalTable]
(
id INT IDENTITY,
label VARCHAR(128)
);
GO

Come back to the SSIS Control Flow. Create a new package scoped variable of String data type. Give it the name TableName and put the Global Temp table name as its value.

aalamrangi.wordpress.com-TempTableInSSIS-4

Go to the Data Flow > OLEDB Source and double click to open the OLEDB Source Editor window. Choose the Data Access Mode as Table name or view name variable. In the Variable name drop-down, choose the new variable that we created. This means that now the OLEDB Source is going to use the GLOBAL Temp table. Of course, it is not the same as the LOCAL Temp table but we will get to that in a minute. Click on the Columns tab to load the table metadata. Then click on OK to close the OLEDB Source Editor.

aalamrangi.wordpress.com-TempTableInSSIS-5

Now add a Flat File Destination and configure its properties. I’ll not go into those details. Please let me know in the comments or via email if you need information on how to configure a Flat File Destination.

The final Data Flow Task looks like this.

aalamrangi.wordpress.com-TempTableInSSIS-6

You can execute the package now to verify if it runs successfully. Although it will run fine, the flat file will not have rows because the source of the data is the Global Temp table, not the Local Temp table populated by the Execute SQL Tasks.

A Global Temp table (or a physical table) is common to all users so it could cause issues in multi-user environments. Local Temp tables are specific to a connection, hence more scalable. All that is needed now is to remove one # in the variable value and the OLEDB Source will point to the correct Local Temp table. To clean up, you can drop the Global Temp table.

aalamrangi.wordpress.com-TempTableInSSIS-7

The flat file will have the rows inserted by the Execute SQL Tasks.

aalamrangi.wordpress.com-TempTableInSSIS-8

Subsequent runs of the package will show validation errors because the Local Temp table is not available when the package starts. To go around this, you can set the DelayValidation property of the package to TRUE. As the package is the parent container for all other tasks, this property will be applied to all tasks in the package. If you do not wish to disable validation for all tasks, then you can set it for individual tasks, i.e. the first Execute SQL Task and the Data Flow Task. Again, the Data Flow Task may contain multiple sources, destinations and transformations and you may not want to disable validation for all of them. In that case you can be more granular and set just the ValidateExternalMetadata property of the OLEDB Source to FALSE.

How To Manage SQL Server Job Failure Emails with MS Outlook Conditional Formatting

Some scheduled jobs send failure emails upon an error and DBAs need to remember to fix the job and re-execute it. I use filter rules in MS Outlook to organize my inbox by redirecting all the scheduled job emails to a separate folder. In addition to that, I use conditional formatting rules to keep track of action items as pending or done. The conditional formatting rules highlight the job failure emails with the red color and turn them green when marked complete as shown in the following image.

JobEmailsAsTasksExpected

Create a Conditional Formatting Rule to Highlight a Failed Job Email in Red:

Navigate to the folder where you would like to modify the view. You can even customize your main Inbox folder view, if that is where your emails are. Right-click on the header row and go to View Settings.

JobEmailsAsTasks1

Click on Conditional Formatting.

JobEmailsAsTasks2

In the Conditional Formatting window, click on Add and rename the new untitled rule. Then change the font color to red and click on Condition.

JobEmailsAsTasks3

In the Filter window, put a sufficiently unique text phrase from the error email that can distinguish it from good emails. Also specify in the drop-down where exactly the filter should look for the text phrase i.e. just in the email subject or, email body or both.

My emails have the following text in the email body that I can use for identification –

STATUS:  Failed

Now this a simple example with a text phrase for filtering but you can use other criteria in More Choices and Advanced tabs too for more complex scenarios as necessary. If you get emails with different text patterns then you can always create a new rule for each phrase.

JobEmailsAsTasks4

That is it. Click on OK to come back to your inbox and verify that the error emails have a red font.

Create a Conditional Formatting Rule to Highlight a Failed Job Email in Green After Resolution:

Now we want another rule that will change the red email to a green one when it is marked as complete. Add another Conditional Formatting rule, rename it and change the font color to green and click on Condition.

JobEmailsAsTasks5

In the filter window, follow the same steps as in the previous red rule. Then go the More Choices tab.

JobEmailsAsTasks6

In the More Choices tab, check the Only Items which: and select the drop-down value of are marked complete.

JobEmailsAsTasks7

Back in the Conditional Formatting window, move the green job up. This is important otherwise the red rule will override the effects of the green rule.

JobEmailsAsTasks8

The final window will look like this.

JobEmailsAsTasks9Final

Now when ever the issue from the failure email is resolved, just right-click on the email, go to Follow Up, select Mark Complete. The email will turn green.

JobEmailsAsTasks10

How To Connect SSMS to ALWAYSON Read-Only Secondary Database

The databases in PRIMARY availability group can be used for read-write access. The databases in the SECONDARY availability group can be used just for read-only access.

An attempt to connect to a SECONDARY availability group database with a normal connection, which is read-write by default, shows the following error message –

Msg 978, Level 14, State 1, Line 1

The target database ('AGDemoDB') is in an
availability group and is currently accessible
for connections when the application intent is
set to read only. For more information about
application intent, see SQL Server Books Online.

To resolve the issue, the connection string needs to have the Application Intent = ReadOnly parameter. How do you pass parameters in a SSMS connection?

SSMS has many options that are not too obvious. One of them is to provide additional connection parameter options. All that is needed to resolve the above error is to use the Additional Connection Parameters screen in the connection dialog and put the parameter there.

AG_ReadOnlyIntent01

The keyword should not have any spaces.

AG_ReadOnlyIntent02

Further reading:

The AlwaysOn Professional MSDN blog has more examples of connection strings for various applications.

You may also like to review the Application Intent Filtering feature of AlwaysOn at this and this link.

How to Avoid Orphan Users in SQL Server ALWAYSON, Create Logins Correctly

SQL Server logins are stored in the [master] database. System databases (master, model, msdb and tempdb) cannot be included in an availability group so a login created on the PRIMARY replica will not show up on the SECONDARY replica automatically. It has to be created manually on the SECONDARY replica.

Logins have an SID associated with them. When a Windows authentication login is created in SQL Server, it uses the SID from the Windows Active Directory. So the SID for such a login will be the same on every SQL Server in the network domain.

When a SQL authentication login is created, it gets a new auto-generated SID assigned by the SQL Server. This auto-generated SID will be different in each SQL Server even if the login name and the password combination are the same.

The database users are mapped to the logins internally using the SID, not the login name/user name. There are some situations where the SID may have a mismatch, e.g. when a database is restored to a different server where the supposedly matching login already exists, or a login is dropped and recreated without consideration to the mapped users, or a login is recreated between a database detach and reattach etc. Such users that do not have a login with a matching SID are known as orphan users. This SID mismatch means that although an application or a user can connect to the server using the login, but it can not access the database.

The following options with varying degree of effectiveness can be used to fix the SID mapping between a login and an orphan user –

  • Drop and recreate the user in the restored database. Of course the user permissions will get deleted too and have to be granted again.
  • Drop and recreate the login with same SID as the restored database. This is a definite no-no if there are other databases on the server linked to that login. It will only complicate matters.
  • Run the system stored procedure sp_change_users_login. It has parameters to just report, fix one or fix all orphans. But the stored procedure is now marked as deprecated so there are no guarantees of future availability.
  • ALTER the user (there are some restrictions, like there cannot be a one-to many mapping etc.) –
    USE [MyUserDBName]
    GO
    ALTER USER someuser WITH LOGIN = somelogin
    

    As the user databases on the SECONDARY replica are read-only, the role of the SECONDARY server has to be changed to PRIMARY by doing a failover before the above ALTER command can be executed.

All of this can be avoided if the login is created correctly on the SECONDARY replica. We just have to make sure that the SID for the login on the SECONDARY replica matches the PRIMARY replica.

Let us begin with creating a new login on the PRIMARY replica of an existing Availability Group –

/* On the PRIMARY replica */

-- Create the Login
USE [master]
GO
CREATE LOGIN [TestLogin]
WITH PASSWORD=N'abc123#'
, DEFAULT_DATABASE=[master]
GO

Grant privileges to this login if necessary. They will NOT automatically replicate to the SECONDARY replica. Now find the SID of this new login.

-- Get the SID for the new Login
SELECT name, sid 
FROM sys.server_principals 
WHERE name = 'TestLogin'

/* Results:
name          sid
TestLogin     0x8EA0E033BD83524180CF813A20C5265B
*/

On the SECONDARY replica, create the login with the same SID. The GUI wizard to create logins does not have this feature to specify the SID, so the login has to be created using TSQL with an additional parameter.

/* On the SECONDARY replica */
-- Create the Login with the same SID as
-- the PRIMARY replica
CREATE LOGIN [TestLogin]
WITH PASSWORD=N'abc123#'
-- use the SID retrieved above
, SID = 0x8EA0E033BD83524180CF813A20C5265B
, DEFAULT_DATABASE=[master]
GO

Grant the same privileges to this login as done on the PRIMARY replica.

Now go back to the PRIMARY replica and create database user mapped to the login and grant required permissions at the database level. This new database user will be automatically replicated on the SECONDARY replica with its permissions and correctly map to the login. No action on SECONDARY required because the user database is in an Availability Group that is synced across replicas.

Further reading:
For the sake of completeness, I must mention the widely cited KB 918992 article (How to transfer logins and passwords between instances of SQL Server) which provides a stored procedure [sp_rev_login] to move the logins from one server to another. This stored procedure generates the CREATE LOGIN script with the password hash and the SID. You would need that stored procedure only if you don’t have access to the clear text passwords or, if you want to include that script as a scheduled job but not hardcode the password in the job. If you do not have those constraints then you can simply use the steps described in my blog post here.