SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the Execute Package Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Learning how to configure and use the SQL Server Integration Services (SSIS) Execute Package Task gives you a great starting point for understanding SSIS package orchestration. I advocate writing small, functional SSIS packages that perform a unit of work. That’s a great idea for many reasons. But it begs the question: “How do I string together the execution of these packages?” Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS Execute Package Task, shown in Figure 1:


Figure 1: SSIS Execute Package Task

The Execute Package Task provides one way to implement an SSIS Design Pattern for SSIS package execution known as the Parent-Child pattern. When an SSIS package uses the Execute Package Task to start another SSIS package, the package with the Execute Package Task is called the Parent and the package started by the Execute Package Task is called the Child.

The Execute Package Task changed between SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The changes support the SSIS 2012 (and 2014) Catalog. There is a new property called ReferenceType which defaults to “Project Reference” in packages executing in Project Deployment Mode (the default mode for building SSIS 2012 and SSIS 2014 SSIS packages), as shown in Figure 2:


Figure 2: Options for the Execute Package Task ReferenceType Property

Project Reference is used to execute an SSIS package – a Child package – in the same SSIS project with the package that contains the Execute Package Task – the Parent package. When Project Reference is selected, the next property in the property grid is PackageNameFromProjectReference, a dropdown containing a list of all the SSIS package in the SSIS project.

Setting the ReferenceType property to External Reference is a way to execute SSIS packages that are stored in the file system or the msdb database. When External Reference is selected the next properties in the property grid change to reflect this backwards-compatible functionality, as shown in Figure 3:


Figure 3: Setting the ReferenceType Property to External Reference

The Location and Connection properties are used to specify an OLE DB Connection (to the msdb database) or a File Connection (to the location of the dtsx file). SQL Server locations also require the name of the SSIS package; the File System option does not because the SSIS package is the only thing in the file. The External Reference ReferenceType setting is useful for importing SSIS solutions that contain earlier versions of the Execute Package Task because External Reference behaves exactly like previous versions of the Execute Package Task.

The remaining properties in the property grid – Password and ExecuteOutOfProcess – are shared between ReferenceType options. Password is used if the SSIS package is password-protected (if the ProtectionLevel property of the SSIS package is set to either EncryptSensitiveWithPassword or EncryptAllWithPassword). ExecuteOutOfProcess is a setting that determines whether the package will be executed as part of the current process or a new process.

When executing SSIS packages in Project Deployment Mode (using the Project Reference ReferenceType), Parameter Bindings are enabled and allow values to be passed from the Parent package to the Child package, as shown in Figure 4:


Figure 4: Binding a Parent Package Parameter to a Child Package Parameter

Parameter Bindings are disabled when the ReferenceType property is set to External Reference.

Once configuration is complete, click the OK button to close the Execute Package Task Editor. You can test execution by pressing the F5 key or selecting “Start Debugging” from the SSIS dropdown menu. A successfully-executed Execute Package Task will appear as shown in Figure 5:


Figure 5: A Successful Execution!

The Execute Package Task drives a powerful data integration architecture pattern: Parent-Child execution. Using SSIS Precedent Constraints and Sequence Containers with the Execute Package Task, a data integration developer can develop SSIS “driver” packages that call Child SSIS packages in any combination of parallel and serial orders of execution.

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)

SQL SERVER – Script to Find Leap Year

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as a variable to @year but you can change it to any year which you want to check for leap year.

DECLARE @YEAR SMALLINT
SET @YEAR=2000
SELECT @YEAR AS YEAR,
CASE
DAY
(EOMONTH(DATEADD(DAY,31,DATEADD(YEAR,@YEAR-1900,0))))
WHEN 29 THEN 'YES' ELSE 'NO'
END AS LEAP_YEAR
GO

The result is

YEAR LEAP_YEAR
 ------ ---------
 2000 YES

The logic is from the given year create a Date which results for Feb 01 of that year. You can do this by adding 31 (DATEADD(YEAR,@YEAR-1900,0) results for Jan 01 of that year). Using EOMONTH finds the last day of the month. Using DAY function find out day value. If it is 29, it is a leap year otherwise it is non Leap year.

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

SQL SERVER – Error: Fix for Error Msg 3906 – Failed to update database because the database is read-only

Some error messages are a great source of troubleshooting when things go wrong. I always request my blog readers to read what the error messages are conveying. Most of the solutions are very much documented as part of the message. I must agree that over the years the error messages have become more descriptive in SQL Server.

On a similar note, recently one of the DB’s sent me a mail stating they were getting the below error message and they thought I had something on the blogs to help them solve the same.

Msg 3906, Level 16, State 1, Line 10

Failed to update database “ProdDB” because the database is read-only.

On careful search, I haven’t seem to have written about the resolution. When I looked at the error message, I thought it was quite explanatory and didn’t need any mention about solution. Your database is marked as ReadOnly and we are not able to write into them. So why the fuss, just mark your database as read-write.

After that incident, I thought of writing this blog to show how this can be solved with a simple code. Let us get into the script next. Let us create our database for testing.

CREATE DATABASE [ReaOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReaOnlyDB', FILENAME = N'C:\Temp\ReaOnlyDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReaOnlyDB_log', FILENAME = N'C:\Temp\ReaOnlyDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
USE ReaOnlyDB
GO
CREATE TABLE tbl_SQLAuth (id INT)
GO

We have gone ahead and created the table which we will use for inserting later in the code. Let us mark the just created database as ReadOnly.

USE MASTER
GO
ALTER DATABASE [ReaOnlyDB] SET READ_ONLY
GO

With the above code successfully executed. Let us go ahead and try to insert some value in the table.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

You will be greeted with the below error message inside SQL Server Management Studio.

Msg 3906, Level 16, State 1, Line 20

Failed to update database “ReaOnlyDB” because the database is read-only.

We have successfully replicated this error message. It is as easy as it gets. If you encounter this error message, we need to remove the ReadOnly attribute from our database. This can be achieved using the following TSQL:

USE [master]
GO
ALTER DATABASE [ReaOnlyDB] SET READ_WRITE WITH NO_WAIT
GO

As simple as it gets. Now the database is available for Write operations.

USE ReaOnlyDB
GO
INSERT INTO tbl_SQLAuth VALUES (1), (2)
GO

Now the insert statement would succeed without any errors. With the error message and resolution discussed. Let me give the cleanup script for this blog.

-- Clean up time
USE MASTER
GO
DROP DATABASE ReaOnlyDB
GO

As I wrap up this blog, would love to hear from you if you have every marked and used ReadOnly databases in your environments? What are the scenario’s you have used these options in databases? Or is this the first time you read about ReadOnly databases? Let me know.

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

Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

Here is the most debated Interview Question – Is Shrinking Database Good or Bad?

I will try to answer this in a single statement – “Shrinking Database is bad practice for performance as it increases fragmentation. It should be used in rare cases of running out of space on drive.”

I believe above statement is not enough to explain the various details associated with it. Here are few reference blog posts, I strongly suggest to read with regards to this subject.

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

SQL SERVER – What are the Different Ways to Script Procedure with T-SQL

Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways.

Let us create this simple procedure

CREATE PROCEDURE TESTING
(@OPTION INT)
AS
IF
@OPTION=0
SELECT 1 AS NUMBER
ELSE
SELECT
100 AS NUMBER
GO

Now if you want to know the script of the above procedure, you can use the following methods

1 Use SP_HELPTEXT System Stored Procedure

EXEC sp_HELPTEXT TESTING

2 Use ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES view

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='TESTING'

3 Use OBJECT_DEFINITION function

SELECT OBJECT_DEFINITION(OBJECT_ID('TESTING'))

4 Use SYS.SYSCOMMENTS system view

SELECT TEXT FROM SYS.SYSCOMMENTS WHERE ID=OBJECT_ID('TESTING')

All the above four methods will show you the script of the procedure names TESTING.

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

SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?

SQL Server has so many things to learn and I always find it amazing. My conversations with customers often come up with security questions esp around SQL Injection. Many have claimed SQL Injection is a SQL Server problem. It takes quite some time for me to let them know there is nothing about SQL Server and SQL Injection. SQL Injection is an outcome of wrong coding practices. One of the recommendations I give is about not using Dynamic SQL. There might be some situations where you can’t avoid it. My only advice would be, avoid if possible. In this blog, I would demonstrate a SQL Injection problem due to dynamic SQL and a possible solution you can have.

Let’s assume that we have a simple search page where user can use blank search or provide filter in any field. We have provided two fields to use “First Name” and “Last Name”. The user types something and hits search. Here is our code of stored procedure which fires behind the scene.

USE AdventureWorks2014
GO
CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,
@lastName NVARCHAR(50)
AS
BEGIN
DECLARE
@sql NVARCHAR(4000)
SELECT @sql = ' SELECT  FirstName ,MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE ''' + @firstName + ''''
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE ''' + @lastName + ''''
EXEC (@sql)
END

If I use this string to execute in last name ”;drop table t1–

EXEC search_first_or_last '%K%', ''';drop table t1--'

The dynamic string would be

SELECT  FirstName, MiddleName, LastName FROM Person.Person WHERE 1 = 1  AND FirstName LIKE '%K%' AND LastName LIKE '';DROP TABLE t1--'

Do you see the problem? Yes, users can drop table t1 if code is running under a high privilege account.

One of the solution of the problem would be to use sp_executesql. Here is the better version using

CREATE PROCEDURE search_first_or_last
@firstName NVARCHAR(50)
,
@lastName NVARCHAR(50)
AS
BEGIN
DECLARE
@sql NVARCHAR(4000)
SELECT @sql = ' SELECT  FirstName , MiddleName, LastName' +
' FROM Person.Person WHERE 1 = 1 '
IF @firstName IS NOT NULL
SELECT @sql = @sql + ' AND FirstName LIKE   @firstName'
IF @lastName IS NOT NULL
SELECT @sql = @sql + ' AND LastName LIKE  @lastName '
EXEC sp_executesql @sql
,N'@firstName nvarchar(50), @lastName nvarchar(50)'
,@firstName
,@lastName
END

Hope you would be able to use this and implement in your project. Are you using these simple techniques in your production code? Have you ever faced similar problems during audit? Do let me know of your learnings.

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

SQL SERVER – Invoking a Stored Procedure from Azure Mobile Services – Notes from the Field #066

[Note from Pinal]: This is a 66th episode of Notes from the Field series. Azure Mobile Services is a very critical aspect and not many people know about it. When I read this article, I find it humorous at points and some of the examples also reminded me of my past experience. If you are in data security, you will have a great time reading these notes, but if you are not, you will still love it.

In this episode of the Notes from the Field series database expert Kevin Hazzard explains Invoking a Stored Procedure from Azure Mobile Services. Read the experience of Kevin in his own words.


One of the questions I’m often asked is whether it’s possible to call SQL stored procedures from Azure Mobile Services. The answer is yes and it’s probably easier than you think. In case you don’t know, Azure Mobile Services is a way to very simply expose an HTTP service over data stored in an Azure SQL database. By default, Azure Mobile Services exposes the SQL tables directly as resources. So the HTTP methods GET, POST, PUT and DELETE will essentially be mapped to SQL operations on the underlying tables.

While this simple mapping mechanism is good for resource-oriented access to the tables, the logic to produce a usable Web API is often a bit more complex than that. Stored procedures can provide an interesting abstraction layer that allows us to use the efficiencies of the SQL Server query engine to reduce round trips to and from Internet clients, for example. Or perhaps stored procedures might be used to hide normalization peculiarities from clients or perhaps to use advanced parameter handling logic. Whatever the case may be, it would be helpful from time to time to be able to invoke stored procedures from the HTTP API that Azure Mobile Services provides.

Let’s start by assuming that an Azure Mobile Service exists with some data that we would like to expose via a stored procedure. For the purposes of this example, my service is called MobileWeatherAlert which contains a backing table in an Azure SQL Database named [MobileWeatherAlert_db]. It’s really helpful that Azure Mobile Services uses schema separation in the underlying database to manage all of its data. That schema separation allows us to expose many separate middle-tier services from one common database if needed. So, in my weather database, there’s a schema called [MobileWeatherAlert] corresponding perfectly to the name of the service that it supports. For the purposes of this example, that schema contains a table called [Observation] which is used to collect weather data by [City].

Figure 1 shows a very simple stored procedure called [GetObservationsForCity] that I’d like to be able to call from the service API.

There are a number of places where this procedure might be invoked. For this example, I’ll implement a custom API in the mobile service called observation.

Figure 2 shows the dialog in the Azure management console where the custom API will be created.

For this simple example, I’ll only implement the HTTP GET method in the API to invoke the stored procedure. For simplicity of the example, I’ll open up access to everyone to avoid having to pass any sort of credentials. Now I can add a bit of JavaScript to the API to make the stored procedure call.

Figure 3 demonstrates adding that JavaScript to the API via the Azure management console.

Lines 1 through 9 in the script encompass the get function that will be invoked when the HTTP GET method is used to call the service. The parameters passed to the JavaScript function are the request and response objects. From the request object, line 2 shows how to obtain a reference to the mssql object which exposes a query function for making calls into the database. Line 3 demonstrates how to call the query function to execute the [GetObservationsForCity] stored procedure, passing a single parameter for the City by which to filter. It’s important to note here that the schema in which the stored procedure resides is not named in the EXEC call. This is counter-intuitive, in my opinion, and is likely to trip up novices as they experiment with this functionality. Since we are invoking the GET method for the MobileWeatherAlert service, there’s an implicit assumption used in the preparation of the SQL statement that objects will reside in a similarly-named database schema.

Notice also on Line 3 that the request object passed into the JavaScript function exposes a query property that conveniently contains an object named city which will be parsed directly from the URL.

Figure 4 shows how that URL might be passed from PostMan, a really excellent Google Chrome plug in that allows the invocation of nearly any sort of HTTP-oriented web service or API.

Finally, lines 4 through 6 of the JavaScript method, the success function that process the results of the SQL query logs the results and returns them to the caller with an HTTP 201 (OK) response. I’ve included a called to the console.log() function to show how easy it is to log just about anything when you’re debugging your JavaScript code in Azure Mobile Services. After invoking an API or custom resource method that logs something, check out the logs tab of the mobile service in the management console to see what got saved. Of course, you’ll want to do minimal logging in production but while you’re testing and debugging, the log is a valuable resource.

In studying the URL and its output in Figure 4, remember that the JavaScript for the observation API didn’t have to do any special parsing of the row set returned by SQL Server to produce this result. Simply returning that data from SQL Server caused the API to emit JavaScript Object Notation (JSON) which has arguably become the lingua franca of the Internet for expressing data.

In closing, I’ll share a couple of thoughts. If you’re interested in building a simple query interface on top of a mobile service, you don’t have to use stored procedures as shown here. Azure Mobile Services implements fairly rich OData support directly on table resources. With OData, filtering, sorting and pagination of SQL data are built in, so to speak. Also, the web way of doing services (sometimes called RESTful based on Dr. Roy Fielding’s dissertation and the HTTP standards that flowed from it), assume that we’ll use HTTP in the way it was intended: accessing and linking resources at a more basic level, using the HTTP methods GET, POST, PUT, and DELETE as a complete, fully-functional language for accessing those resources. Database people inherently understand and respect this access pattern better than many programmers working in traditional programming languages like C# and Java. After all, we’re accustomed to using four basic methods to manipulate data in our databases: SELECT, INSERT, UPDATE, and DELETE. Yet, as database people, we also know that giving software developers strict table-level access can cause all sorts of performance problems. For those situations, where you know that some complex database operation could be performed much more efficiently with a bit of T-SQL code, a stored procedure or a view may be just the prescription your developers need. Hopefully, this article has helped you understand how to invoke programmatic resource in a SQL Azure database and perhaps it will help you along the way to making the correct architectural choices in the design of your modern, data-driven web applications.

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

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