Suppress the Error Number, Severity Level and State Number in the Error output

For any reason, if you don’t want to show the Error Number, Severity Level and the State Number along with the Error Message, use the Severity Level 0 (Zero) or 10. The Severity Number 10 is converted to Zero internally.

Demonstration:

A severity number except 0 or 10 displays the Error Number, Severity and State information.

SuppressErrorNumber1

A severity number 0 or 10 suppresses the Error Number, Severity and State information.

SuppressErrorNumber2

Further Reading:

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086(v=sql.105).aspx

Use SSIS to Export Clickable URLs to Excel

When SSIS is used to export data to a MS Excel workbook, Excel tries to guess the data type from the first row of data. (Tip: This can be overridden by the IMEX=n parameter in the Excel connection string, where n is the number of rows you want it to scan). As it happens, Excel can not always win the guessing game (or maybe it just plays it safe) and treats data as plain text. Sometimes even numbers and URLs are treated as text and an apostrophe is appended at the beginning of the data value.

If you have a requirement to export hot (i.e. clickable) URLs to Excel, you can use the workaround that I am going to demonstrate below.

We will use the following SQL table. The sample data is intentionally in bad format. We will fix it with a query -

create table URLDemo
(
 id int identity
,url varchar(100)
,friendlyname varchar(100)
);

insert into URLDemo
(url, friendlyname)
values
 ('www.google.com', 'Google')
,('http://www.yahoo.com', 'Yahoo')
,('bing.com', NULL);
-- Data in table ------------------------
select id, url, friendlyname 
from URLDemo

-- Clean data with query ----------------
select
id,
convert(varchar(100),
case
when left(url, 4) = 'www.' then 'http://'+url
when left(url, 11) <> 'http://www.' then 'http://www.'+url
when left(url, 7) <> 'http://' then 'http://'+url
else url
end
) as url,
convert(varchar(100),
case
when isnull(friendlyname, '') = '' then url
else friendlyname
end
) as friendlyname
from URLDemo

ExcelURLDemo1

The Clean Data query is an attempt to add any missing http://www. to the URL and also show a friendly name if missing. These will be useful later for the Excel HYPERLINK formula.

The problem -

We can export this data to Excel, even try to encode the Excel HYPERLINK formula as a derived column, the url and the formula will show up just as plain text, that is they will not be clickable.

The workaround -

Create an Excel Workbook that will be the template for data export. Type in the column names [id], [url] and [friendlyname] for column A, B and C respectively. Put the following Excel formula in the column D :-

=IF( AND(B1<>"", C1<>""), HYPERLINK(B1,C1), "")

Drag this formula cell to the number of rows as appropriate for your expected data volume. I did it for the first 20 rows. The idea here is to populate the [url] and [friendlyname] columns via SSIS and let the formula create a clickable link for us.

ExcelURLDemo2

Then select cells A1 to C1 and give this range a name, e.g. MyDataRange.

ExcelURLDemo3

Now create an SSIS package. Add a Data Flow Task and double-click to open its designer. Add a OLEDB Source in the Data Flow designer. The query in this editor is the Clean Data query above.

ExcelURLDemo4

Add a Data Conversion Task. It will be used to convert non-unicode (VARCHAR) columns namely [url] and [friendlyname] to unicode. Excel likes to have character data in unicode.

ExcelURLDemo5

Add an Excel Destination. It will use the data range name. Verify the column mappings.

ExcelURLDemo6

 

Gyaan: Excel tries to identify the last “dirty” row in the worksheet and puts new data from SSIS in the next row. In my case it will be row 21 because I dragged the formula till row 20. Even if the cells once used are emptied by deleting the values/formulas, they are still considered “dirty”. The only way to remove the “dirty” flag from a cell is to delete the whole row! If the package will put data in row 21 and beyond, we miss out on the formulas that we put in the first 20 rows. Using the range tells Excel to look for dirty cells only within the range, thus allowing us to use the formulas in the adjacent cells.

Now verify the column mappings. Note that Excel columns [url] and [friendlyname] are mapped to the [urlWSTR] and [friendlynameWSTR] of the Data Conversion.

ExcelURLDemo7

That is it, execute the package and click away at the URLs!

ExcelURLDemo8

Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection

One of the looping constructs available in SSIS packages is the Foreach Loop Container. One of its enumerator options is the For Each Item Enumerator. It enumerates through a list of items populated at design time. The list is static at execution time, i.e. the list items can not be dynamically loaded from a configuration file or a another source like an SQL table. Probably due to the static nature of the item list, this enumerator type is not used too often in packages.

On the other hand, there are some scenarios where a static list can prove to be useful. For example -

  • no need to create a database table just to pass on values for the Foreach Loop
  • enforce a specific order to the parameters passed to the loop
  • initialize variables values with the Item Enumerator instead of doing that in a Script task

An another frequent requirement in SSIS packages is to execute a set of operations against different SQL Servers or databases. The package should change the OLE DB connection string dynamically during execution to point towards the appropriate target. A looping construct iterates through the list of servers and databases, and the package Expressions change the OLE DB connection properties.

I am creating this step-by-step scenario to demonstrate the use of a Foreach Item Enumerator and use it to make a dynamic OLEDB connection. A single query is executed in a loop against three different SQL databases and the output is exported to a single text file.

The steps -

Add a Foreach Loop Container to your SSIS package. Double-click on it to open its editor. Click on the Collections tab on the left of the editor. Select the enumerator as Foreach Item Enumerator. Click on the Columns… button. Add two columns of String type. As you add columns, the editor gives them names like Column0, Column1 and so on, and there is no way to customize these names.

ItemEnumeratorDemo1

Populate the column values by simply typing in the rows. Use Column0 for server names and Column1 for corresponding database names on that server. I have used my local server for all three rows but you can use your own server names here. This is the list of items that are iterated by the Foreach Loop.

ItemEnumeratorDemo2

Now you need two variables that will hold the values shredded from the item list as the Foreach Loop iterates through the rows. These variables are used later in the Property Expressions of the OLEDB connection to make it dynamic.

Click on Variable Mappings tab on the left and then on the <New Variable…>

ItemEnumeratorDemo3

Add two variables of String type, namely ServerName and DatabaseName.

ItemEnumeratorDemo4

The Index 0 and 1 of the variables correspond to the Column0 and Column1 of the Foreach Item list respectively.

ItemEnumeratorDemo5

That is all there is to it. Now the loop is ready to iterate through the item rows.

Let us move on to the dynamic OLEDB connection. Add a Data Flow Task to the Foreach Loop Container.

ItemEnumeratorDemo6

Double-click the Data Flow Task to view its design surface. Now add one OLE DB Source.

ItemEnumeratorDemo7

Double-click OLE DB Source to set its properties. First window is of its connection manager.

ItemEnumeratorDemo8

Once the connection information is done, you will view its properties editor. Set the Data Access Mode to SQL Command and put the following query in the SQL Command Text -

select
@@SERVERNAME as ServerName
,DB_NAME() as DatabaseName
,GETDATE() as LogDateTime
,COUNT(*) as NumberOfIndexes
from sys.indexes;

ItemEnumeratorDemo9

Clicking the Columns tab is important so that the SSIS can find the metadata like column names, data types etc. from the underlying tables/views. If you don’t do this then you’ll find later that SSIS does not know of this metadata.

ItemEnumeratorDemo10

Again, it is a good idea to drag the connector from OLEDB source to the Flat File destination before modifying the Flat File destination properties. In this way, the SSIS Designer helps you by matching the destination with the source metadata automatically. Otherwise you have to perform some extra steps to match columns. I hope you like to save time as much as I do!

ItemEnumeratorDemo11

Give a file path and check the box for Column Names in the First Row.

ItemEnumeratorDemo12

Verify the column mappings in the Mappings tab.

ItemEnumeratorDemo13

The trick to make the OLEDB connection string dynamic lies in the next couple of steps where you edit the properties of the OLEDB connection and assign expressions to its ServerName and InitialCatalog properties.

ItemEnumeratorDemo14

The property ServerName gets the variable @[User::ServerName] and the property InitialCatalog gets the variable @[User::DatabaseName]. Remember that these variables will get their values modified during each iteration of the Foreach Loop and as a result the OLEDB connection string will get modified too.

ItemEnumeratorDemo15

The last step is just to make sure that data is appended to the destination text file instead of being overwritten with each loop iteration and new source connection.

ItemEnumeratorDemo16

15 years of experience with Identity columns

Aalam Rangi:

A succinct refresher on SQL Server IDENTITY columns.

Originally posted on SQL Studies:

  • An identity column is an auto incrementing column
  • An identity column is typically used as a primary key
  • A primary key that’s an identity column is called a surrogate key
  • A surrogate key is one that is not related to the contents of the row in any way
  • An identity column must be NOT NULL
  • You can tell if a column is an identity column by looking at the is_identity column of sys.columns or using the COLUMNPROPERTY function (TableObjectId, ColumnName, ‘IsIdentity’)
  • An identity column has three parts. Data type, Seed, and Increment
  • The data type of an identity column is typically an INT but can be most numeric data types. ie tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)
  • The seed defaults to 1 and is usually 1. It can however been any value that fits in the data type.
  • IDENT_SEED returns the original seed value of a table
  • To change…

View original 272 more words

Find Top 25 Inefficient Query Plans by CPU, IO, Recompiles, Execution Count

Microsoft TechNet Gallery is a treasure trove of scripts that can save you a lot of coding time or sometimes introduce creative ways of solving a challenge.

I came across this collection of scripts that will show you the top 25 inefficient query plans (in XML format) sorted by CPU, IO, recompiles, execution counts etc.

Download: http://gallery.technet.microsoft.com/Find-inefficient-query-88f4611f

SSIS Components Naming Conventions

I’ve written earlier about my naming conventions for variables in SSIS packages that help me figure out the source of the variable value easily. This post continues on to the prefix-based naming conventions I use (or plan to use) for the other components in SSIS packages.

While looking around online for SSIS component naming conventions, most of the suggestions are based on Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. He has tried to limit the prefixes to 3 or 4 characters and included most of the frequently used components. Using the prefixes as described in his post has helped me a lot in the maintenance phase of a package when my memory has faded about the functionality and the workflow. Some more motivation to adopt a naming convention can be found in 31-days of SSIS series by Jason Strate (Blog | @StrateSQL).

My goal was to make an exhaustive list of components with their prefixes so that -

  • I do not need to put on my thinking hat every time I start using a component for the first time.
  • The naming convention can be future-proof and avoid confusion with new components being introduced in SSIS platform.
  • Remove the guess-work (especially for new SSIS developers and maintenance teams who may not be SSIS developers primarily) by avoiding prefixes that sound too similar to another.

I created an Excel sheet of all the components (more than 120) that I found listed in MSDN, i.e. containers, connection managers, sources, destinations, tasks and transformations. My prefixes are more elaborate than Jamie’s for the reasons mentioned above, but that is just my preference. You can use the Excel as a template for you organization and modify the prefixes to you liking.

The following is an example of one section from the Excel.

Download: Get the full workbook at the TechNet Galleries

Example -

SSIS Component Naming Conventions

Keeping up with the spirit of spring season, I’ve refreshed the look of the blog by using the “Flounder” theme. I was using the “Enterprise” theme earlier.

What I like about Flounder is :

  • the colour scheme
  • the overall font choice
  • a well-defined sidebar
  • and most of all, its mobile device compatibility. It readjusts from 3-column layout to a 2-column or a 1-column layout as the screen size becomes smaller. That really allows very easy browsing on a tablet or a cell phone, for folks who don’t use the WordPress app, by completely avoiding horizontal scrolling.

I have also changed the blog’s tag line from “Learnings as a SQL Server DBA and Developer” to “Erudition as a SQL DBA and Developer”.

er·u·di·tion [er-yoo-dish-uhn, er-oo-]

noun
knowledge acquired by study, research, etc.; learning; scholarship.

And finally, I’ve added my Twitter handle @AalamRangi to the publicize section of the blog so that a new post is announced on Twitter too. I’m not a big Twitter user right now but I plan to learn more about it in this year.