Dynamic Connection Strings in SSIS

If you need the ability to change connection strings in your SSIS packages without opening each one and hard coding a string, then dynamic connection strings are your answer. There are many different ways to do this, including Package Configurations and Parameters (2012), but here I’ll be explaining how to use variables to dynamically change the connection string using information stored in a flat file or database table that can be manipulated without opening the SSIS package. This make server renames / upgrades / moves much easier since the information only has to be changed in one place.

Starting out, we’ll need the source data. I’ve created a table with 2 columns, ConnectionName and ConnectionString along with some test data.

CREATE TABLE ConnectionStrings
    (ConnectionName VARCHAR(256)
    ,ConnectionString VARCHAR(256))
INSERT INTO ConnectionStrings
    (ConnectionName
    ,ConnectionString)
        VALUES  ('<ConnectionName>',
        'Data Source=<Server>;Initial Catalog=<Database>;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
    )
            

Once the data has been populated in the table, we can open up a new SSIS project in the SQL Server Data Tools (2012) or BIDS (2008/2005) application and add a new OLE DB Connection. At this point it doesn’t matter what connection string is used since it will be overwritten at execution time, but for testing purposes you will want to point it to your test system.

Next ,we will create a new variable scoped to the package called SourceConnectionString with a Data Type of String.

Now, select the Source connection in the Connection Manager and expand Expressions. This is where we’ll assign the SourceConnectionString variable to the ConnectionString of the Source connection via expressions.

Finally, we need to populate the SourceConnectionString variable by querying the connection string table we setup in the first step. Once we have the results from the query, we’ll assign the results to the variable, which in turn assigns a connection string to the Source connection. This can be accomplished by doing the following.

Under the Execute SQL Task Editor, the result set should be set to Single Row and the SQL Statement should read “Select ConnectionString From ConnectionStrings”

Next, under Result Set, assign Result Name 0 to the variable that you created for the connection string.

Now that everything is set, the Source connection string value will be filled with the value that you placed in the ConnectionStrings table.

Connect Item: Filter Tables and Views in SQL Server Import and Export Wizard

If you've ever used the SQL Server Import and Export Wizard inside SSMS on databases with hundreds of objects you know that it can be difficult to navigate the source tables and views grid looking for the object or objects you need. So to aleviate this I've created a Connect item to add the ability to filter objects when selecting the source tables and views. If you've had this problem or think this is a worthwhile suggestion, please vote it as important.

You can view the Connect item here:
https://connect.microsoft.com/SQLServer/feedback/details/732418/filter-tables-and-views-in-sql-server-import-and-export-wizard

Thanks,
Kevin

Extracting a Query Execution Plan from the Procedure Cache

From SQL Server 2005 forward, it has been possible to view the execution plan of a query that has been executed by someone other than yourself. This is extremely useful on procedures that you, as a DBA, would not want to execute in production because of data modification logic, but are used everyday as a part of the application. Finding out how the procedure is running and why it is running so poorly without having to execute it also saves a lot of setup time since many procedures require an extensive set of input parameters that we may or may not know.

First, a little setup. We need to create a test stored procedure that we will be looking for in the procedure cache.

CREATE PROCEDURE KevinsTestProc

AS
BEGIN
  SELECT TOP 1 * FROM sys.objects
END
GO

After we’ve created the stored procedure, execute it a couple times so that SQL Server will cache it. Once it is there, we can start querying various execution related DMV’s that will eventually show us the execution plan.

EXEC KevinsTestProc

GO

Since there can be an enormous amount of plans in the procedure cache, the first thing we need to help narrow down the search is some text that is unique to that procedure, like the procedure name.  By querying the sys.dm_exec_cached_plans DMV and using the sys.dm_exec_sql_text DMF we can get the plan handle will be used to extract the exection plan.

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan

FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts > 1
AND text LIKE '%KevinsTestProc%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;

Results:

We can see from here that we have the plan handle,  how many times this store procedure has been executed, how large the plan is, the text inside the cached plan, and an XML representation of the Execution Plan generated at compile time.

Now that we have this we can click on the XML hyperlink in the results window to open up the execution plan in SSMS.

Finding Open Cursors (And Their Locks) In SQL Server

If you need information regarding open cursors on your SQL Server, you can query the sys.dm_exec_cursors DMV to get information on when they were created and their various properties. With that DMV along with the sys.dm_exec_sessions DMV and the sys.dm_exec_sql_text DMF to get the SQL that was used to create the cursor.

SELECTc.session_id
,c.cursor_id
,c.properties
,c.creation_time
,c.is_open
,c.fetch_status
,c.dormant_duration
,s.login_time
,t.text
FROM
sys.dm_exec_cursors (0) c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) t

If you need to see if one of those cursors is holding locks on your system, you can use the sys.dm_tran_locks and sys.dm_exec_sessions DMV 's along with the sys.partitions catalog view to see the cursor and the table it has locked.


SELECTOBJECT_NAME(P.object_id) AS TableName
,L.resource_type
,L.resource_description
,L.request_session_id
,L.request_mode
,L.request_type
,L.request_status
,L.request_reference_count
,L.request_lifetime
,L.request_owner_type
,s.transaction_isolation_level
,s.login_name
,s.login_time
,s.last_request_start_time
,s.last_request_end_time
,s.status
,s.program_name
,s.login_name
,s.nt_user_name
--,c.connect_time
--,c.last_read
--,c.last_write
--,t.text
FROM
sys.dm_tran_locks L
JOIN sys.partitions P
ON L.resource_associated_entity_id = P.hobt_id
JOIN sys.dm_exec_sessions s
ON L.request_session_id = s.session_id
--JOIN sys.dm_exec_connections c
--ON s.session_id = c.most_recent_session_id
--CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE
L.request_owner_type = 'CURSOR'
ORDER
BY L.request_session_id

Starting a SQL Agent job with Powershell and Windows Scheduled Tasks

In the past, I’ve needed to start SQL Agent jobs using Windows Scheduled Tasks. Using Powershell, this is now easier than ever.

First, let’s create the Powershell script. It’s very basic and relies on Windows Authentication and SQL Native Client to connect to the SQL Server.

#Vars for Server and JobName
$Server = "<Server>"
$JobName = "<JobName>"

#Create/Open Connection
$sqlConn = new-object System.Data.SqlClient.sqlConnection "server=$Server;database=msdb;Integrated Security=sspi"
$sqlConn.Open()

#Create Command Obj
$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"

#Exec Command
$sqlCommand.ExecuteReader()

#Close Conneection
$sqlConn.Close()


In a nutshell, this script will need you to set 2 variables, $Server and $JobName. Once those have been set, the script will open up a connection to the server, execute the sp_start_job command and then close the connection.

Next, we want to start the Powershell script via Windows Scheduled Tasks. When setting up the task, choose "Start a program" under Action and type powershell.exe . We specify the Powershell script to execute in the additional arguments. By adding &'\\<filepath>\StartTest_SQL_AgentJob.ps1' Powershell will start up and execute the script.

Note: If your <filepath> has spaces in it, you will need the "&", otherwise it can be omitted.

OT: Recharging a defective Craftsman 19.2 battery

Several years ago, I received a Craftsman 19.2v cordless drill set as a gift. It came with 2 batteries and, over the years, the drill and the batteries have served me quite well. About a year ago after a particularly harsh winter, I tried to charge the batteries but the charger showed them as defective. Turns out that if the battery goes completely dead, the charger believes that it is defective and will not charge them.

Thanks to the power of the Internet, I was able to "jump start" my dead batteries to where the charger would not see them as defective and charge them. I should note that the idea was not my own but came from the user FongBar via this post.

Since I believed that both of my batteries were beyond repair, I had already purchased a new battery that was fully functional. Having a fully functional and charged battery is important because this is how we will "jump start" the old battery.

To start, I went to Harbor Freight and bought these CEN-TECH 18" Low Voltage Multi-Colored Test Leads.These will be used to transfer the charge from one battery to the other.

Once you have the leads, the charged battery, and the defective battery, you are ready to hook up the two to start transferring the charge.

When you set both batteries down, you will see the terminals are setup in a cross shape. The two batteries will be connected in parallel, meaning that using the leads, you will connect the top terminal on the good battery to the top terminal on the bad battery. The same goes for the left, right and bottom terminals.

Once the batteries are hooked together, let them sit from 20 - 30 minutes. Once the time has passed, I put the old, defective battery on the charger and it started charging again. This procedure only has to be done once and your battery should charge normally from this point forward.

Disclaimer: This tutorial is without guarantee and all liability rest solely with the reader.

Resource Governor - A Practical Example

If your running SQL Server 2008 Enterprise and have the need to restrict certain users or groups from executing runaway queries, then the Resource Governor might be what you are looking for. By creating the right mix of workload groups and resource pools, a DBA can create an environment that is more predictable and stable, while maintaining the availability of resources for all users.

In this example, I'll create a Workload Group that only Windows logins will use. I used the distinction of Windows logins vs SQL logins because all the produciton applications hitting this server use SQL authentication while any users (aside from the sys admins) doing ad hoc analytics use Windows authentication. Many times when users are doing ad hoc analytics, they are digging through the data and looking for trends, which lends itself to someone using "Select *".  That kind of query on very wide and deep tables can easily lock a table needed for the production applications. This is where the resource governor can help. By limiting the amount of resources a query or a session can have, a DBA can make other sessions on the server execute more predicitbly.

There is quite a bit of setup involved with the Resource Governor and there are many different ways to do it, so that steps I take below will obviously have to be tailored to your enviornment.

There are 3 main parts to setting up the Resource Governor:
   1.       Resource Pool: A pool of resources that Workload Groups will access.
   2.       Workload Group: Group that logins belong to based on the Classifier Function.
   3.       Classifier Function: Function that assigns logins to Workload Groups.

First , let's do the setup for the classifier. Since we are classififing on login type (SQL or Windows) our Classifier Function should be quite simple, but the Classifier Function has to be schema bound, which means we can't reference sys.server_principals. So since we can't use the system tables in the Classifier Function, we need to create our own table in the MASTER database to keep SQL login information.

CREATE TABLE [SQLLoginsList]
(SQL_LoginName sysname)

Now we need to populate the table with all the existing SQL Logins.

Insert Into SQLLoginsList
Select Name From sys.Server_Principals Where [Type] = N'S'

Once the table is in place, a server trigger needs to be created to insert into the SQLLoginsList table any time a new SQL Login is created.

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [srv_trg_SQLLoginList] ON ALL SERVER
       WITH EXECUTE AS 'sa'
       FOR CREATE_LOGIN
AS
       SET NOCOUNT ON
       SET CONCAT_NULL_YIELDS_NULL ON
       SET ANSI_PADDING ON
       SET ANSI_WARNINGS ON

       IF (SELECT   EVENTDATA().value('(//LoginType)[1]', 'VARCHAR(255)')) = 'SQL Login'

          BEGIN
                IF NOT EXISTS ( SELECT  *
                                FROM    SQLLoginsList
                                WHERE   SQL_LoginName = EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)') )
                   BEGIN
                         INSERT dbo.SQLLoginsList(SQL_LoginName)
                         SELECT  EVENTDATA().value('(//ObjectName)[1]','VARCHAR(255)')
                   END  
          END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [srv_trg_SQLLoginList] ON ALL SERVER

GO

Now that we have a place to query SQL Logins and a mechanism to catch newly created ones, we can start creating our Resource Pool and Workload Group.

When creating a Resource Pool, there are 4 properties that can be set.

   1.       MIN_CPU_PERCENT: The minimum CPU bandwidth for the pool when there is CPU contention.
   2.       MAX_CPU_PERCENT: The max CPU bandwidth for the pool when there is CPU contention.
   3.       MIN_MEMORY_PERCENT: minimum amount of memory reserved for this pool.
   4.       MAX_MEMORY_PERCENT: maximum amount of memory requests in this pool can consume.

The "when there is CPU contention" because CPU contention is a soft limit, meaning when there is no CPU contention the pool will consume as much CPU as it needs.

CREATE RESOURCE POOL pAdhocProcessing
WITH
 (MIN_CPU_PERCENT = 0 --no min cpu bandwidth for pool WHEN THERE IS CONTENTION
 ,MAX_CPU_PERCENT = 25 --max cpu bandwidth for the pool WHEN THERE IS CONTENTION
 ,MIN_MEMORY_PERCENT = 0 --no memory reservation for this pool
 ,MAX_MEMORY_PERCENT = 25 --max server memory this pool can take
 )

Next comes the creation of the Workload Group. By default there is already a group called "default" that all users are placed into if they aren't placed into another Workload Group by the Classifier Function. Think of the Workload Groups as buckets of users that can dip water (resources) from an assigned "pool" (Resource Pool). Each Workload Group can only have one Resource Pool, but a Resouce Pool can have many Workload Groups assigned to it.

When creating a Workload Group, there are 6 properties that can be set.

   1.       IMPORTANCE: Gives a precedence or "importance" to CPU resources available in the Resource Pool.
   2.       REQUEST_MAX_MEMORY_GRNT_PERCENT: Amount of memory one request can take from Resource Pool.
   3.       REQUEST_MAX_CPU_TIME_SEC: Amount of total CPU time a request can have.
   4.       REQUEST_MEMORY_GRANT_TIMEOUT_SEC: Maximum amount of time a request will wait for resource to free up.
   5.       MAX_DOP: Max Degree of Parallelism a query can execute with. This option takes precedence over any query hint or server setting.
   6.       GROUP_MAX_REQUESTS: Amount of requests this group can simultaneously issue.

The following script will create the Workload Group and assign it to a Resource Pool

CREATE WORKLOAD GROUP gAdhocProcessing
WITH
  (IMPORTANCE = LOW --Low importance meaning the scheduler will execute medium (default) session 3 times more often
  ,REQUEST_MAX_MEMORY_GRANT_PERCENT = 25 --one person can only take 25 percent of the memory afforded to the pool
  ,REQUEST_MAX_CPU_TIME_SEC = 60 --can only take a TOTAL of 60 seconds of CPU time (this is not total query time)
  ,REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60 --max amount of time a query will wait for resource to become available
  ,MAX_DOP = 1 --overrides all other DOP hints and server settings
  ,GROUP_MAX_REQUESTS = 0 --unlimited requests (default) in this group
  )

 Using pAdhocProcessing

At this point, we should see the following under Management --> Resource Governor



The Resource Governor will stay in "Reconfigure Pending" status until we create the Classifier Function and issue a Reconfigure Command to turn the Resource Governor on.

Finally, we'll create our Classifier Function that will place all incoming requests into either the gAdhocProcessing group or the default group. This function looks a lot like any other scalar function with the excepting that is had to be created using "With SchemaBinding". Based on the login type and whether the login is a sysadmin, the function will return what Resource Group the request should fall into.

CREATE FUNCTION fnLoginClassifier()

RETURNS sysname
WITH SCHEMABINDING
as
Begin
  Declare @Login sysname

  set @Login = SUser_Name()

  if (IS_SRVROLEMEMBER('sysadmin') = 1)  --Server Admins

    Return N'default'
  else if (Select Count(*) from dbo.[SQLLoginsList] Where SQL_LoginName = @Login) > 0 --SQL Logins
    Return N'default'
  else if @Login like '<domain>%' --Windows Logins
    Return N'gAdhocProcessing'

  --If nothing is returned, put it in the default group just in case

  Return N'default'
End
GO   

Now that the Classifier Function has been created we have to tell the Resource Governor to use that function as it's classifier.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnLoginClassifier)

At this point, if we right click the  Resource Governor in the Object Explorer and choose properties we will should see the following.

The Resource Pool pAdhocProcessing has been created and the Workload Group gAdhocProcessing has been assigned to it. Also, the fnLoginClassifier function shows as the Classifier function name and the message at the top signals us that the Resource Governor has pending changes and that we need to issue a Reconfigure command to enable the governor.

Now all that's left to do is turn it on. We can do that by issuing the following command.

ALTER RESOURCE GOVERNOR RECONFIGURE

Once the Resource Governor is turned on, we can monitor the amount of sessions each login has open and what Workload Group they have been assigned to.

SELECT  WG.name

       ,COUNT(*)
       ,ES.login_name
       ,es.program_name
FROM    sys.dm_exec_sessions ES
        INNER JOIN sys.dm_resource_governor_workload_groups WG
        ON ES.group_id = WG.group_id
GROUP BY WG.name,es.login_name,es.program_name       
ORDER BY login_name 

If for some reason the Resource Governor is not working as it should, issue the following command to disable it.

ALTER RESOURCE GOVERNOR DISABLE

Since it is possible on a busy system to lock yourself out by configuring the Resource Governor incorrectly, you may have to sign in with the Dedicated Administrator Connection (DAC). That connection uses the internal Workload Group and cannot have it's resources altered. Once you have established a connection using the DAC, you will have the ability to either disable the Resouce Governor or remove the Classifier Function. If you remove the Classifier Function, all incoming connections will fall to the default group. To remove the Classifier Function, issue the following commands.

ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = NULL)
ALTER RESOURCE GOVERNOR RECONFIGURE