SQL SERVER – The Basics of the SSIS Execute SQL Task – Notes from the Field #044

[Note from Pinal]: This is a new episode of Notes from the Field series. SSIS is an interesting concept. There are more hidden features with SSIS than any other product. Additionally, there are many visible features which are not known to most of the people. Earlier I mention to SQL developer that they can execute SQL queries from SSIS they looked at me like they have seen the ghost. They always thought that they can only do the task in SSIS which are available to them in the tool box. Well, I think when I will get the similar question next time, I am going to forward them a link to this blog post. Andy has explained in very simple words how one can execute SQL Task in SSIS.

With “SQL Server” included in the name of SQL Server Integration Services (SSIS), it is easy for people to assume SSIS is simply a database tool or accessory. Let there be no mistake, SSIS is a software development platform. To give you a better understanding of how to use SSIS as a development platform, I’ll write a series of blogs that step through how to use Control Flow tasks. This first article will cover the Execute SQL Task.

When developing solutions with SSIS, I use a handful of Control Flow tasks:

  • Execute SQL Task
  • Data Flow Task
  • Script Task
  • Execute Package Task
  • File System Task
  • Execute Process Task

This list is a good approximation of which tasks I use most, too – from most-used to least-used. In this article I provide a basic example of configuring the SSIS Execute SQL Task, shown in Figure 1:

Figure 1: SSIS Execute SQL Task

Three things are required to configure an Execute SQL Task:

  1. Connection Type
  2. Connection Manager
  3. SQL Statement

Connection Type

The default Connection Type is OLE DB, as shown in Figure 2:

Figure 2: Available Connection Type Property values

I configure Execute SQL Tasks to execute SQL statements like truncate a table, update or insert a single row of data, or call a stored procedure. I typically use OLE DB or ADO.NET connection types; but I occasionally use ODBC connection types. (When executing parameterized statements I find ADO.NET offers a cleaner interface. I will cover parameters in another article).

After selecting a connection type, selecting the Connection Manager is next. The Execute SQL Task Editor filters the list of available connection managers, displaying only connection managers of the (connection) type configured in the previous step.

Figure 3 shows how to select a Connection Manager:

Figure 3: Selecting a Connection Manager

The last required step to configuring an SSIS Execute SQL Task is to supply an SQL Statement. There are two properties for this: SQLSourceType and SQLStatement. The SQL Source Type property specifies the source of the SQL Statement as one of the following:

  • Direct Input
  • File Connection
  • Variable

The SQL Statement can be entered manually (direct input). It can be stored in a file (file connection) or the SQL Statement can be stored in an SSIS variable (variable). In most cases you will manually enter the query as shown in Figure 4:

Figure 4: Entering the Query

Right-click the task and click “Execute Task” to test the task configuration. Or simply press the F5 key to execute the entire SSIS package, as shown in Figure 5:

Figure 5: Success!

You now know the basics of configuring an SSIS Execute SQL Task. Go code!


If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Live Plan for Executing Query – SQL in Sixty Seconds #073

Yesterday I wrote blog post based on my latest Pluralsight course on learning SQL Server 2014. I discussed newly introduced live plans. For query before you execute it, you can see the estimated execution plan and after you executed you can see the actual execution plan – however, how to see a query plan while the query is still being executed. This is a very interesting question. In SQL Server 2014 we have now a new DMV, which tracks execution stats at each operator level while the query is still running. The DMV for the same issys.dm_exec_query_profiles.

I hope my earlier blog post clearly explained how live plan for executing query works. If not, I suggest you watch following quick video where I explain this concept in extremely simple words.

You can download the code used in this video from Simple Demo of New Cardinality Estimation Features of SQL Server 2014.

Action Item

Here are the blog posts I have previously written. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Live Plans for Long Running Queries

If you are a SQL Server DBA or developer who is majorly dealing with queries which are running for a long time, you may have one request every time you see a long running query. The request is how to see a plan for a query when it is still running. For query before you execute it, you can see the estimated execution plan and after you executed you can see the actual execution plan – however, how to see a query plan while the query is still being executed. This is a very interesting question.

Well with SQL Server 2014 we have now a new DMV, which tracks execution stats at each operator level while the query is still running. The dmv for the same is sys.dm_exec_query_profiles.

First execute any long running query. In this example, I will build a test table.

-- Create a table to host data
CREATE TABLE Customers (ID INT, Name CHAR(2000) DEFAULT 'SQLAuthority')

Right after that, I will populate it with random data in loop.

-- This can take upto a minute or two based on your system.
DECLARE @loop  INT = 1
WHILE @loop <= 100000
Customers (ID) VALUES (@loop)
SET @loop = @loop + 1
GO 3

Now I will enable actual execution plan for the query. You can enable that with CTRL + M. If you forget this step, you will be not able to see the execution plan in the next script.

Next, execute sample query which will take long time to execute.

FROM Customers c
INNER JOIN  Customers c1 ON c1.ID = c.ID
WHERE c.ID > 50

Now I will run DMV, which will show me live plans at operator level for the query.

-- Open this on a different session and run it.
SELECT physical_operator_name, row_count, estimate_row_count, session_id
FROM sys.dm_exec_query_profiles

Well, that’s it. It is that simple. You can see various operators and its plan in the next image. This is very handy, when your query is going to take long time to execute.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Know Cardinality of Executed Query?

Earlier I wrote three blog posts on SQL Server 2014 Cardinality. Read them over here to get the context of this blog post.

I explained in the last blog post that you can execute query in different cardinality even though your database has different compatibility. One of the questions which I have received right after the blog post was how does the user know what is the compatibility of the query which is just executed. It is indeed a very good question. There are multiple ways to know the cardinality of the query just executed. Here are two ways to do the same.

1) Execution Plan Properties

I enabled execution plan (CTRL+M) when executing queries and right after that I selected one of the operators of the execution plan. Right after that I right clicked and selected properties of the execution plan. You can also enable the properties with shortcut key F4 as well. It will display the properties window on the right side in SSMS.

Over here see the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.

 2) XML Execution Plan

This method also requires you to enable execution plan with (CTRL+M). Once the execution plan is enabled right click over the execution plan and click on the option Show XML execution plan. Once this option is clicked it will open a new window with XML plan.

In the XML execution plan, look for the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.

Well, it is very simple to know what is the cardinality of query execution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – 3000th Blog Posts and Thank You

3000th Blog Posts! 

When I realized that I am writing my 3000th blog post (list of all of my blog posts), I just got blank with happiness. I could not come up with what exactly to write. It has been a long time since I have stopped writing about milestones. I believe writing about milestone is not helpful to users, it is just plain bragging about oneself. However, when I reach 3000th blog post. I am just stunned and not sure what to talk about. I will now answer three questions which I have received about this blog in the last month, maybe that will give some insight in my blog post.

Q: How can you write every single day from last 7 years?
A: It is about to be 8 years soon. I just enjoy it. Every day, I write one blog post before I start my daily routine. May be this discipline has helped me. I have talked about secrets of this in my latest Play by Play: Pinal Dave  Course.

Q: Do you have a day job or you just do blogging?
A: Yea, I have a day job where I go to the office, work for over 8 years, take a lunch break and have amazing people to work with. I work for Pluralsight as an Evangelist. However, blogging is my passion and heart beat. That is why I have recently written a book on Secret Tool Box of Successful Bloggers: 52 Tips to Build a High Traffic Top Ranking Blog and it is doing quite well.

Q: I want to learn more about blogging, is there any other resource besides your book do you suggest?
A: Well, I suggest you read my book as that is the latest additional to my library. It is 12th book I have authored in last 3 years and a very first non-technical book. If you do not want to read books, you can watch my Pluralsight courses on Building a Successful Blog and Building a High Traffic, Profitable Blog. Both of the courses are very unique in nature and gives perspective in my methodology in blogging.

Bonus Q: If you have a day job and you spend so much time in writing books as well as authoring video courses – when do you sleep?
A: Well, let me quote my favorite author Robert Frost.

The woods are lovely, dark and deep,   
But I have promises to keep,   
And miles to go before I sleep,   
And miles to go before I sleep.

I am just humbled and thank you to all of you. I really mean it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – CTRL + R Shortcut Does Not Work in SSMS

I just formatted my computer and installed fresh SQL Server 2014. The very first thing which I did was to open SQL Server Management Studio and execute a sample query. As soon as I executed the query, it returned me some result. Once I did not need the result, my next instinct was to hide the result as I did not need it any further. I am very used to the keyboard shortcut CTRL + R to hide the result. As soon as I typed in the CTRL + R the result pane was still visible. My pressing CTRL+R did not do anything further.

I suddenly remembered that last time when I had installed SQL Server 2012 I had faced the similar situation when a few of my shortcuts did not work. Here is what I did to make my shortcut work again.

Steps to Get Shortcut working again:

  1.  Go to Tools Menu in SSSMS
  2. Click on Option
  3. Go to Keyboard and expand the menu
  4. Click on option Keyboard again
  5. Keep the option on the drop down – Apply the following additional keyboard mapping scheme as (Default)
  6. Click on Reset

and you are done!

Well, on the internet, there are many different solutions and many of them are valid in different scenarios. In my scenario following above steps had fixed my issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2

Seven years ago, I wrote a blog post about – Query to Find Seed Values, Increment Values and Current Identity Column value of the table. It is quite a popular blog post and lots of people like it as it gives immediate details about Identity Values. SQL expert Mark Hickin has further improved this query and posted an interesting modification where the query also display upper limits of the data type used in the query. You can read the blog post over Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype.

However, this query has a small limitation that it only works in those cases when increment value is 1 and seed is 1. However, if you have any other value as identity or seed, you will need a bit more modification in the script. SQL Server Expert and Guru Harsh has provided amazing script where he has provided query with the said adjustment.

SELECT Seed,Increment,CurrentIdentity,TABLE_NAME,DataType,MaxPosValue ,
FLOOR((MaxPosValue -CurrentIdentity)/Increment) AS Remaining,
100-100*((CurrentIdentity-Seed)/Increment+1) / FLOOR((MaxPosValue - Seed) /Increment+1) AS PercentUnAllocated
INNER JOIN ( SELECT name AS Data_Type ,
POWER(CAST(2 AS VARCHAR), ( max_length * 8 ) - 1) AS MaxPosValue
FROM sys.types
WHERE name LIKE '%Int'
) t ON c.DATA_TYPE = t.Data_Type
'IsIdentity') = 1
ORDER BY PercentUnAllocated ASC

Here is the screenshot of the image. Thanks Harsh- a very nice query.

Reference: Pinal Dave (http://blog.SQLAuthority.com)