SQL SERVER – The Basics of the SSIS Script Task – Notes from the Field #065

[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 SSIS script 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) Script Task gives you a great starting point for learning how to use SSIS Control Flow tasks. You’ll find that SQL Server Control Flow tasks provide powerful development capabilities that may surprise you. Let there be no mistake, 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:

This article will focus on a basic example of configuring the SSIS Script Task, shown in Figure 1. People often think the SSIS Script Task is just for generating log messages, but let’s look at some of its more powerful capabilities.

Figure 1: SSIS Script Task

The Script Task supports Microsoft Visual C# and Microsoft Visual Basic languages. You can choose which language you will use prior to clicking the Edit Script button. Once you’ve clicked the Edit Script button, you cannot change the Script Language property.

At the heart of the Script Task is the ability to read a variable value from the SSIS package into the script and then write a message out of the Script Task. Because the Script Task can interact with SSIS variables, you can use .Net code to manipulate and respond to variable values.

For the purpose of our demonstration, we will add code to the Script Task to create a message for SSIS logging.

Messaging is a key component of enterprise architecture. Messages are generated (published, raised, produced, etc.) by Senders and received (subscribed, consumed, requested, etc.) by Listeners. Messaging is a complex topic. Entire books are dedicated to the topic.

Add variables to the ReadOnlyVariables and ReadWriteVariables properties by clicking the ellipsis inside the value textbox for each property. When you click the ellipsis, a Select Variables dialog appears, as you see in Figure 2.

Figure 2: Adding Read Only Variables to the Script Task

SSIS variables use a two-part naming convention: <Namespace>::<VariableName>. I added the SSIS variables System::TaskName and System::PackageName. The selected variables are in the System namespace and contain the name of the task and package, respectively.

Click the Edit Script button to open the script editing window as shown in Figure 3:

Figure 3: The Script Task Script Editor

Add the following code beneath the instruction to Add your code here:

Dim sPackageName As String = _ Dts.Variables("System::PackageName").Value.ToString
Dim sMsg As String = "I am " & sPackageName
Dts.Events.Fireinformation(1001, sPackageName, sMsg, "", 0, True)

Listing 1: VB.Net Code

The first line of the code in Listing 1 creates a VB.Net variable named sPackageName and initializes the value of this variable, setting it to the value of the SSIS Variable named System::PackageName. This is one way to read SSIS Variable values into a Script Task’s script.

The second line of the code in Listing 1 creates a VB.Net variable named sMsg and initializes the value of this variable with the text, “I am” plus the value contained in the sPackageName VB.Net variable.

The third line of the code shown in Listing 1 calls the FireInformation method in the Dts.Events assembly. FireInformation accepts six arguments:

  1. InformationCode – an integer you can use to identify custom messages sent from the SSIS package.
  2. SubComponent – a string you can use to populate the source of the message.
  3. Description – a string containing the message you wish to send.
  4. HelpFile – a string containing the location of a Help file (should a help file exist).
  5. HelpContext – an integer pointing to the help context (should a help context exist).
  6. FireAgain – a Boolean value indicating whether the event should subsequently fire.

My VB code includes a reference to the FireAgain argument. Note: This MSDN post states: “Because firing of an event may be expensive, the run-time engine provides a mechanism for suppressing events that you are not interested in. Every event firing method has a FireAgain parameter. If the value of this variable is false, after the method returns, the caller will not fire this event again for the duration of the current execution.” I understand this to mean setting FireAgain to False would suppress future FireInformation events in my Script Task, but testing indicates this is not correct. This is important for you to know because you may opt to use the FireAgain argument to manage when you raise events. I do not use the FireAgain argument for this purpose. I typically manage the decision to raise events in conditional logic surrounding the Event-raising code.

A test execution of the package reveals an Information message recorded on the Progress / Execution Results tab as shown in Figure 4.

Figure 4: the Information Message on the Progress / Execution Results Tab

Information messages generated from Script Tasks will appear in SSIS logs and the SSIS Catalog. With this information, you can monitor the progress of SSIS execution while the package is running, or view the historical status of variable values or package messages after the package execution has completed.

The SSIS Script Task can accomplish much more than generating log messages. This brief introduction and basic example have demonstrated how to get started configuring and using the SSIS Script Task. As you can see, SSIS Script Tasks give you development capabilities you may not have been aware of.

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)

Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 2 of 3)

I am very fortunate that I know John Sonmez in person. He is one of those individuals who understands the young mind of developers and depth of software development industry. I am a regular follower of his books, videos and ideas. I have personally implemented many of his ideas in my personal life and I have seen dramatic improvement. Earlier this month, I requested him to share his views and suggestions with all of us on this blog. He kindly accepted the request and returned with three amazing blog posts and a plethora of giveaways.

Let us read the John Sonmez in his own words in this second part of a three part series. You can read the first part of this series over here.

Welcome back, this is the second post in my three-part series where I am bringing you three of my best tips, from my new book, “Soft Skills: The Software Developer’s Life Manual”, to help you land your dream job.

You can find part one of the series here.

Oh, and don’t forget the giveaway for How To Market Yourself as a Software Developer. I’ll be doing at the end of the next post. You won’t want to miss that, so bookmark this page and check back tomorrow.

Tip #2: Get a personal referral

Sure, getting a personal referral is easy enough if you already know someone at the company you are applying for, but what if you don’t?

It can still be done. You just might have to work a little harder at it.

Start with who you know that might know someone at the company you are applying for. Ask them to introduce you and offer to buy them a cup of coffee—or even a nice lunch.

But, do yourself a favor, don’t try and “sell them” or “network,” instead just have a friendly chat and try to get to know them a little better.

A little known secret to the interview process is that interviewers almost always hire people they like. Yes, technical skills are important, but often the deciding factor in who gets the offer and who gets the rejection letter has completely to do with who the interviewer liked more.

Now, the person who refers you for a job might not be the same person who interviews you, but if you want to get a good referral, the same principle applies.

What if you don’t know someone who knows someone at the company?

If you don’t have someone that can make the introduction for you, make it yourself.

Try and find a few employees at the company you are applying at and see if they have blogs or a twitter account. Comment on their blogs or start interacting with them on social media.

There is almost always a way to reach someone at a company that you want to work at.

Today, the internet makes it very easy for you to build relationships with people at companies before you even apply there.

If you are serious about landing your dream job and increasing your salary, you should never apply for a job without a personal referral.

It might take some time to build up a relationship, but getting that personal referral can make it so you are not only a lot more likely to get the job, but also more likely to get a higher offer.

I talk about how I used this technique to land a six-figure job, working from home in “Soft Skills”, so if you are interested in getting more detail on this technique, go check it out.

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

SQL SERVER – How to Know Backup History of Current Database?

Some blog post and scripts are like ever green references. One such blog has been Get Database Backup History for a Single Database. A number of you have pinged me and for amendments to that script with various ideas. Since that post was written more than 4+ years now, I thought it would be worthwhile to change it to current trends of SQL Server 2014 additions. What are the changes made to the previous script? The script will answer few questions like:

  • What were the different backups taken on the current DB? Remove the condition mentioned below and it can be generic enough for all DBs on your server.
  • Which user was involved in taking the backup?
  • What is the type of backup we are talking about?
  • Recovery Model and Database compatibility level of the DB at the time the backup was taken. I remember using this for a user, who used to change the DB compatibility in their script before a backup. It was basically a copy-paste problem from an internet script. This data helped there.
  • Size of backup – Both compressed and uncompressed.
  • If the Backup was password protected.
  • Finally, when the backups were taken.

So let us look at the script next. Feel free to modify the same as you wish.

-- Recent backup history for the current DB
SELECT s.database_name 'Database',
s.recovery_model 'Recovery Model',
s.USER_NAME 'Backup by Username',
'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
END 'Backup Type',
CONVERT(VARCHAR(20), s.backup_finish_date, 13) 'Backup Completed',
CAST(mf.physical_device_name AS VARCHAR(100)) 'Physical device name',
DATEDIFF(minute, s.backup_start_date, s.backup_finish_date) 'Duration Min',
CAST(ROUND(s.backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Size in MB',
CAST(ROUND(s.compressed_backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Compressed Size in MB',
CASE WHEN LEFT(mf.physical_device_name, 1) = '{' THEN 'SQL VSS Writer'
WHEN LEFT(mf.physical_device_name, 3) LIKE '[A-Za-z]:\%' THEN 'SQL Backup'
WHEN LEFT(mf.physical_device_name, 2) LIKE '\\' THEN 'SQL Backup'
ELSE mf.physical_device_name
END 'Backup tool',
s.is_force_offline /* for WITH NORECOVERY option */
FROM   msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE  s.database_name = DB_NAME() AND  -- remove this condition if you want all DBs
s.backup_finish_date > DATEADD(MONTH, -3, GETDATE()) -- Get data for past 3 months
ORDER BY s.backup_finish_date DESC;

A sample output would look like:

There can be more additional fields added to this script like: If encryption is enabled, Collation information, LSN information, if backup has checksum etc. Do let me know which additional information do you use in your environment to know your backups better.

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

Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 1 of 3)

I am very fortunate that I know John Sonmez in person. He is one of those individuals who understands the young mind of developers and depth of software development industry. I am a regular follower of his books, videos and ideas. I have personally implemented many of his ideas in my personal life and I have seen dramatic improvement. Earlier this month, I requested him to share his views and suggestions with all of us on this blog. He kindly accepted the request and returned with three amazing blog posts and a plethora of giveaways.

Let us read the John Sonmez in his own words in this first part of a three part series.

You’ve probably invested a lot of time learning about SQL and databases.

That’s great. If you are reading this blog, you are probably the kind of person who really cares about your work and has a passion for what you are doing.

You are already probably ahead of 90% of your peers.


What if I told you that there was another way to get even further ahead—a way that most technical professionals ignore?

Well, there is. In fact, what I am about to teach you in this 3-part series of posts, are some of the soft skills that I used to increase my income by over 500% (yes, that is 5 times—no typo), and live a happier, much more fulfilling life.

Oh, and if that isn’t enough for you. If you hang around for the last post in this 3-part series, I’ve got something extra special for you. I’m going to be giving away How To Market Yourself as a Software Developer.

Most technical professionals think getting a good job is about showing how technically competent you are.

That is exactly what I used to think, so I focused all my efforts on raising my technical skills and learning how to solve hard problems on a whiteboard.

That was before I discovered that the majority of high-paying jobs are given to people who figure out how to get in through the back door—not those who are the most technically competent.

I know it’s difficult to believe.

I had a hard time believing it myself, but many sources—including this NY Times article—are showing that a majority of hires are coming from other sources than the traditional submit-a-resume-and-pass-an-interview process.

So, if you want to land that perfect job, you’ve got to be more than just technically competent.

In my new book, “Soft Skills: The Software Developer’s Life Manual,” I’ve dedicated a whole section of the book to improving your career, landing your dream job and increasing your income. In the next few posts, I’ll share with you some of my best secrets from the book. (You can check out the first chapter for free here.)

Tip #1: Start and maintain a highly focused blog

Pinal Dave has done an excellent job of this and he is reaping the benefits. Do you think Pinal Dave would ever have to do an interview for a position at a company hiring a SQL expert? I don’t think so. I think most employers would want to hire him on the spot, just because of his excellent reputation—which he built mostly from his blog.

I, myself, have been given job offers—completely bypassing the interview process altogether—because the interviewer had been reading my blog.

The key is to get started and be consistent.

I once interviewed Pinal Dave for a course I was creating on how to market yourself as a software developer and he told me that he woke up every morning and wrote a blog post before he even brushed his teeth.

His dedication to writing every day has allowed him to earn a great income off of this blog and to reach around 2 million technical professionals who read this blog every month.

Whenever I speak at conferences about marketing yourself and soft skills for technical people, I always ask how many people have a blog. Usually about half of the room will raise their hand. But, guess what happens when I ask how many people have posted on their blog weekly, for at least the last year?

Often out of a room of 200-300 developers, there will be just 1 or 2 hands raised.

Want to set yourself apart?

Want to stand out and get noticed and have employers knocking down your door to hire you?

Start a highly focused blog—that means pick a specific topic, like Pinal did with SQLAuthority—and blog at least once a week.

Anyone can do it and it’s much easier than you think. To find out more about how to create a successful blog, either check out my blogging chapter in Soft Skills or you can enroll in this free 3-week email course I created that takes you through the process step-by-step.

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

SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

Errors are a great starting point for learning. Especially, my inbox is always flooded with atleast 2-3 error messages almost every other day. People keep asking me how to solve them, what to do when we encounter an error and so on. Long time back I wrote a simple blog which talks about error message 5133 while creating database. You can read it here:

SQL SERVER – Error : Fix : Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.)

One of the readers sent me email that she is simply running Create Database <DatabaseName> command and still get the above error. In my blog I mentioned “There must be some typo or error in filepath” but in this case there is no path given. This time I wanted to reproduce the error and was intrigued to why they were getting this error with the simple TSQL Create database command.

I spent some time researching and spoke to few friends about this. They informed that there is a setting in SQL Server which has default path of database files which is picked if nothing is specified. And it made complete sense. Based on this information,  I was finally able to reproduce the error by following his instructions. Here is the error:

Msg 5133, Level 16, State 1, Line 14
Directory lookup for the file “E:\InvalidPath\SQLAuth.mdf” failed with the operating system error 2(The system cannot find the file specified.).
Msg 1802, Level 16, State 1, Line 14
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

If you look at the path it is “E:\InvalidPath\SQLAuth.mdf”. The path is picked from the below registry value:


In my case, I have SQL 2014 named instance called SQL2014 – that’s why we are seeing MSSQL12.SQL2014

Here is the T-SQL way to get the values.

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Default_Data_path]
SERVERPROPERTY('InstanceDefaultLogPath') AS  [Default_log_path]

We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.

Following is the T-SQL way to achieve the change.

USE [master]
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData', REG_SZ, N'E:\InvalidPath'
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog', REG_SZ, N'E:\InvalidPath'

NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.

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

SQL SERVER – Error: Msg 701, Level 17, State 103. There is insufficient system memory in resource pool to run this query

Talking and exploring In-Memory topics inside SQL Server 2014 has been interesting to me. When I wrote the blog around table variable not being just an In-Memory structure, one of my course listener (SQL Server 2014 Administration New Features) pinged me on twitter to ask, if In-Memory OLTP was really In-Memory? Wouldn’t SQL Server like to swap the data or memory data to pagination file when there is memory pressure? I told them the concept of In-Memory is that data always resides in memory and the reason for feature name “In-Memory OLTP”.

The next question that came out of this interaction, what happens when we hit the memory boundary? Will SQL Server error out even if there is Memory available in the system or do something else. This was an easy setup to try.

Script Usage

We will create an In-Memory database, restrict it to have limited memory and add some rows to see if there is any error. The basic 4 steps to follow are:

-- Step 1: Create the Resource Pool. Limit to 5% memory.

-- Step 2: Create the InMemory DB
FILENAME = 'd:\data\InMemoryData.mdf', size=100MB),
-- Memory Optimized Data
NAME = [InMemory_InMem_dir],
FILENAME = 'd:\data\InMemory_InMem_dir')
LOG ON (name = [InMem_demo_log], Filename='d:\data\InMemory.ldf', size=50MB)

-- Step 3: Bind the resource pool to DB
EXEC sp_xtp_bind_db_resource_pool 'InMemory', 'InMemory_Pool'

-- Step 4: For RG to take effect, make DB Offline and Online

After this we will create a wide table and add rows to exhaust the memory for this resource pool.

USE InMemory
-- Step 5: Create a Memeory Optimized Table
Name CHAR(8000) NOT NULL

-- Step 6: Add a lot of rows to get the error
@counter AS INT = 1
WHILE (@counter <= 1000000)
dbo.DummyTable_Mem VALUES(@counter, 'SQLAuthority')
SET @counter = @counter + 1

The Step 6 will not complete because of insufficient memory. We will get an error as shown below:

The statement has been terminated.
Msg 701, Level 17, State 103, Line 49
There is insufficient system memory in resource pool 'InMemory_Pool' to run this query.

To add more rows to the table:

  • Make sure more memory is allocated to the resource pool and reconfigure the same.
  • Delete some rows from In-Memory tables on this database to make space for new allocations.

Let me help you clean up the script after this experiment.

-- Clean up

To learn such interesting SQL Server 2014 enhancements, feel free to listen to my Pluralsight course for more such topics.

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

Interview Question of the Week #004 – List All Columns and Their Data Type for a View

Earlier this week I wrote a blog about finding stored procedure parameters and their data types. After that blog I received few emails from my blog readers asking for similar script for a view. I asked them what did they like about the script and they said that it gives us base datatype and user defined data type as well. So I have put some more efforts to write similar script about view. This is a fine example of how each one of you out there inspire me with some great content ideas. These keep these emails coming my way.

Here is the question – How to list all columns and their datatype for a view in SQL Server?

Usage of script

To use the below script you need to replace the view name (vEmployee in sample code) and schema name (HumanResources in sample code). Also make sure you are in same database which has stored procedure (AdventureWorks2014 is sample code):

USE AdventureWorks2014
@SchemaName NVARCHAR(4000)
SELECT   @ViewName = N'vEmployee'
,@SchemaName = N'HumanResources'
,CAST(ISNULL(ic.index_column_id, 0) AS BIT) AS [InPrimaryKey]
FROM sys.foreign_key_columns AS colfk
WHERE colfk.parent_column_id = c.column_id
AND colfk.parent_object_id = c.OBJECT_ID
), 0) AS BIT) AS [IsForeignKey]
,u_t.NAME AS [DataType]
,ISNULL(b_t.NAME, N'') AS [SystemType]
c.max_length <> - 1
THEN c.max_length / 2
ELSE c.max_length
END AS INT) AS [Length]
,CAST(c.PRECISION AS INT) AS [NumericPrecision]
,CAST(c.scale AS INT) AS [NumericScale]
,c.is_nullable AS [Nullable]
,c.is_computed AS [Computed]
,ISNULL(s.NAME, N'') AS [XmlSchemaNamespaceSchema]
,ISNULL(xsc.NAME, N'') AS [XmlSchemaNamespace]
CASE c.is_xml_document
), 0) AS [XmlDocumentConstraint]
,CAST(c.is_sparse AS BIT) AS [IsSparse]
,CAST(c.is_column_set AS BIT) AS [IsColumnSet]
,c.column_id AS [ID]
FROM sys.all_views AS v
INNER JOIN sys.all_columns AS c ON c.OBJECT_ID = v.OBJECT_ID
JOIN sys.indexes AS i ON i.OBJECT_ID = c.OBJECT_ID
AND 1 = i.is_primary_key
LEFT JOIN sys.index_columns AS ic ON ic.index_id = i.index_id
AND ic.column_id = c.column_id
AND 0 = ic.is_included_column
LEFT JOIN sys.types AS u_t ON u_t.user_type_id = c.user_type_id
LEFT JOIN sys.types AS b_t ON (
b_t.user_type_id = c.system_type_id
AND b_t.user_type_id = b_t.system_type_id
   OR (
b_t.system_type_id = c.system_type_id)
       AND (
b_t.user_type_id = c.user_type_id)
       AND (
b_t.is_user_defined = 0)
       AND (
b_t.is_assembly_type = 1)
LEFT JOIN sys.xml_schema_collections AS xsc ON xsc.xml_collection_id = c.xml_collection_id
LEFT JOIN sys.schemas AS s ON s.schema_id = xsc.schema_id
WHERE (v.TYPE = 'V')
   AND (
v.NAME = @ViewName
AND SCHEMA_NAME(v.schema_id) = @SchemaName

Here is the sample execution. I have highlighted the modification needed to use the script.

I hope these scripts will help you in your environments. I would love to hear back from you how these can be enhanced if possible.

Click to Download Scripts

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