As I learnt a few things about blogging, I made a list of tips to get better at blogging. Thought I would share.

1. Pour your heart out to answer a question. When someone asks a question, it means they followed your post hoping they would find an answer to their problem. If they are stuck, take responsibility. Do everything you can do to help them.
2. Don’t plagiarise. It is just not the right thing to do.
3. Don’t like your own posts. We know you like them, you don’t have to explicitly click like to show that.
4. Don’t start with Hi or Hello. You’re writing a blog, not a letter.
5. Don’t sweat it. Write what comes to you and how it comes to you.
6. Be natural. You’ll be at your best when you are yourself.
7. Use images. Images deliver the message quickly. If it’s a technical post, throw in lot of screen prints, else find a funny and relevant picture to use.
8. Name your images. One extra opportunity do drop keywords for SEO. Search engines read image names and rank your post better. Posts also show up in image search.
9. Use lists.
10. Check spellings. There is no excuse for spelling mistakes.
11. Check grammar. Difficult specially for people with foreign mother tongues (including me). It’s okay to be not perfect, but keep an eye on it.
12. Check font. Inconsistent font size looks ugly. Will make the readers go away.
13. Title is the key to a good post. Choose a title to attract people, not Google.
14. Make use of URLs. Google will read URL text to show your posts in search results. People don’t read this. Feel free to use as many keywords as you want.
15. Let the readers comment. Don’t post a comment saying you received so and so feedback from somebody.
16. Be careful with series of posts. They are both powerful and dangerous. In depth series like this by Devin Knight, for example, will make the readers come back, a series on keyboard shortcuts won’t.
17. Use your energy wisely. Blogging requires a lot of energy: time and thought. Use it and write to solve problems.
18. Blog because you want to, not because someone else is blogging.

As always, comments are most welcome.

@SamuelVanga

You might constantly see a warning message that appears like the one below when creating Analysis Services Tabular projects. It basically says, workspace database server ‘ServerName’ was not found.

You’ll have to change this setting from the model properties. The server should be an Analysis Services Server running in Tabular mode.

You’ll have to deal with this every time. It’s such a pain. Right? Fixing it for good is easy. Simply click on Tools, go to Options and expand Analysis Services. Change default workspace server and default deployment server to an Analysis Services server instance that’s running in tabular mode.

~Sam.

I hear you. You’ll use the Execute Package Task. This mechanism of executing one package from another is popularly knows as Parent Child paradigm. Let me tell you, this isn’t new in 2012. There are, however, a few changes to the Execute Package Task. Let’s take a look.

Demoland!

I added two packages to the project and conveniently named them Parent.dtsx and Child.dtsx.

Child Package

In the child package, I added a script task and used the following script. This will display a message box and tells that it is executing.

MsgBox(“Howdy! I’m the child package”)

Parent Package

In the parent package, I added an Execute Package Task and renamed it to Run Child Package.

In the Package page of the Execute Package Task editor, there is a new property called reference type. It is set to Project Reference by default. This means you can point the execute package task to another package within the same project. I selected Child.dtsx from the drop down.

The following is the output when I execute the parent package.

In prior versions, you’ll have to choose either file system or SQL Server as the source for child package and have connection managers to all the child packages in the parent. For example, if you have one parent package calling 50 child packages, the parent needs 50 connection managers to each of those child packages. This is still supported for legacy packages – change the reference type property to External Reference.

Passing a variable from parent to child

You often pass variables from parent package to the child package; connection strings, parent package start time, parent package name (ok. May be). Again, in the previous versions, you would use a parent package configurations to do this.

In this example, I’ll pass parent package name to the child package.

Another change to the Execute Package Task is parameter binding. You can map a parameter or a variable from the parent package to a parameter in the child package.

In the below image, I created a parameter in the child package. I wrote about SSIS parameters in an earlier post.

From the parent package, open the Execute Package Task Editor. In the parameter bindings page, I mapped the child parameter to the parent’s PackageName system variable.

Then I changed the script in the child package as follows:

MsgBox(“Howdy! I’m the Child Package. I was called by ” & Dts.Variables(“$Package::ParentName”).Value.ToString)

When I execute the parent package, I see…

Zip It

In this post, I looked at using Execute Package Task to call a package from another and pass variable from parent to child.

@SamuelVanga

I frequently attend a few online training events that greatly help me learn new things. Thought I would share, if may be you are interested too. These are all cool people sharing information for free to the community.

Go get'em!

• PragmaticWorks Webinars. Training events on the T’s – Tuesday’s and Thursday’s covering all areas of SQL Server. (t: @PWFreeTraining)
• Brent Ozar PLF Tech Triage Tuesday. Also on Tuesdays, either Brent Ozar or Kendra Little or Jeremiah Peschka offer training on areas covering database administration, performance, storage, and much more. (t: @BrentOzarPLF)
• Andy Leonard SSIS MicroTraining. Andy provides great examples on designing SSIS frameworks and design patterns. (t: @AndyLeonard)
• PASS Virtual Chapters. Sponsored by PASS and others, these events are organized by various virtual chapters. Business Intelligence virtual chapter is my favorite.

All the sessions are recorded and posted on their respective blogs. If you miss the live event you can always catch up later.

Do you know something else that isn’t here? Please share. Drop a comment below. And SPREAD THE WORD, would ya!

~Sam.

Andy Leonard blogged about a gotcha when creating SSIS projects using SSDT yesterday. He showed that you can use any component in SSDT whether or not your SQL Server supports that component. For example, you can create a package with Fuzzy Lookup and deploy to a server running Business Intelligence edition (Fuzzy Lookup isn’t supported on this edition), you won’t even be warned until the package bombs when executed from command line or SQL Agent.

Rafi Asraf made a comment on that post about how it plays well with SSAS projects. I’ll try to elaborate that comment here.

There is a property called Deployment Server Edition for SSAS projects.

SSAS Multidimensional.

SSAS Tabular.

I selected Standard edition. Perspectives aren’t supported in this edition. When I try to create them, SSDT shows a warning message. This is friendly.

Now, why a similar property isn’t available for SSIS projects? That, my friend, is beyond the scope of my brain. Adding it will definitely save a lot of time and reduce confusion for developers.

~Sam.

Follow Sam on Twitter – @SamuelVanga

There are a few design choices that can make your SSIS packages run super fast when loading data from table A to table B.

SQL Command at Source

OLE DB source editor allows you to choose either a table or a view from a drop down if data access mode is Table or View. This will execute a SELECT * command on the data source. As a best practice, it is always better to list the columns even when you are selecting all of them. Choose SQL Command and type in the query.

Table or View – Fast Load at Destination

As the name suggests, Table or View – Fast Load is the fastest way to load data to destination. It applies a table lock on the destination table and performs bulk insert. It is in arguably the fastest way to insert data.

My package on a machine with 8GB RAM and 4 processors ran for 58 seconds to load 7.9 million records. Compare this to 50 minutes when using Table or View as the data access mode. This option works similar to a cursor – inserts one record at a time. Clear evidence for it’s poor performance.

50 minutes? hell No! But 58 seconds to load about 8 million records isn’t bad. Right?

Let’s dig a little deeper to see if we can tune this even more.

Default Buffer

SSIS relies heavily on buffer. A set of records are read into the buffer, and from buffer they are written to the destination. This process continues until all rows are written to destination. For instance, in this example, buffer is carrying 9,216 rows at a time. You can see that by using data viewers.

This number is controlled by two properties DefaultMaxBufferRows and DefaultMaxBufferSize. The values are 10,000 rows and 10MB by default, whichever comes first. MaxBufferSize is 100MB – it’s the maximum size of rows that can be fitted in buffer.

EstimatedRowSize is another property that is calculated based on the metadata of our result. This is 32 bytes for my data set. (BIGINT = 8B + INT = 4B + DATETIME = 8B + INT = 4B + MONEY = 8B) = 32 Bytes.

Buffer reached the default maximum allowed with 9,216 rows, that amounts to a size of 9,216 (rows) * 32 (bytes for each row) = 294912 Bytes which is less than 1MB, remember maximum is 100MB. There is a lot of free space left on the buffer that can be used. Filling this will result in shorten trips thus increasing performance.

For this test, I left DefaultMaxBufferSize as 10MB, but increased DefaultMaxBufferRows to 30,000 rows. The package now runs in less than 30 seconds.

Changing the settings to DefaultMaxBufferSize = 90MB and DefaultMaxBufferRows = 60,000 rows resulted in the package to execute in 15 seconds.

Wrap Up…

Understanding the internals sometimes will yield great performance without the need for additional hardware. This package is running on my local machine and data is being moved within the same database. Off course results might vary depending on your environment.

~Sam.

Welcome to another week of SQL Roundup. Just in case you missed, SQL Server 2012 was released this week and you can download the evaluation edition here.

Clever and great improvisation. Create tag cloud style reports using SSRS. (Jason Thomas).

Jen McCown summarizes the agony caused during the SQL Server Virtual Launch Event.

Use this decision matrix to choose from the different BI tools. (Melissa Coates).

Don’t miss out on an opportunity to receive free SQL Server training.

So, wassup with BISM? Simran Jindal clearly explains the meaning of BISM!

Cheers!

Follow Me:

Following in the foot steps of John Sansom (b|t), I’m going to post a roundup of links from other blogs every Friday. I’ll call this SQL Roundup. John calls his “Something for the Weekend“.

Each Roundup will have no more than 10 links. Trust me, I don’t want to ruin your Friday with too much information. Areas i hope to cover include: T-SQL, SQL Business Intelligence, Data Warehouse, Blogging, Professional Development. These links will mostly be new blog posts, but occasionally I’ll point to older ones too.

Here is the first round:

SQL Roundup for 03/02/2012

Review: I Done This Andy Warren reviews a product and concludes his post with a tip that all new bloggers will find useful.

How To Write an Interview Winning CV You rock when your CV rocks. John shares some excellent ideas on writing a CV.

So, what is the BI Semantic Model? Chris Webb walks you back to early SSAS days and compares UDM with BISM.

SSIS – Name Those Connections by Andy Leonard. Your DBA will give you a hug. Not sure if you would want it though!

Why Use SSAS Tabular? by Marco Russo. SSAS 2012 comes in tabular mode, along with the traditional Multi-Dimensional mode, Alright. But, why use it?

Watch out for some activity on Twitter with #sqlroundup hash tag.

Enjoy!

Follow Me:

Problem

I’ve a SSIS package. When it fails execution, I want to write a custom message including the package name and error description to a text file.

Solution

One way to accomplish this is to declare a variable, populate it with the message you want, and write it’s value to a file using a script task. Then add this script task to an OnError event handler so it executes in response to an error in the SSIS package.

1) Declare a variable: I’ve declared a package scoped variable called vFullErrorMsg.

2) Configure an OnError event handler for the package. So, the tasks in the event handler will execute when the package fails.

3) Add a script task: I renamed the task to Write To Log On Failure.

4) Edit Script Task: Add ErrorDescription,PackageName as a read only variable and vFullErrorMsg as a read write variable.

5) Use a script similar to the one below:

Public Sub Main()

‘declare a variable for error description
‘write DTS variable ErrorDescription to local variable vErrorDescription
Dim vErrorDescription As String = Dts.Variables(“ErrorDescription”).Value.ToString

‘declare a variable for package name
‘write DTS variable PackageName to local variable vPackageName
Dim vPackageName As String = Dts.Variables(“PackageName”).Value.ToString

‘Write error string to DTS variable
Me.Dts.Variables(“vFullErrorMsg”).Value =
“ERROR: ” & ” Package [" & vPackageName & "] Failed” & “. Full Description: ” & vErrorDescription

‘ write DTS variable to a file using stream writer
Using sw As StreamWriter = New StreamWriter(“D:\Documents\ErrorLog.Txt”, True)
sw.WriteLine(Dts.Variables(“vFullErrorMsg”).Value.ToString)

End Using

Dts.TaskResult = ScriptResults.Success
End Sub

With this set up, you should see the error message written to D:\Documents\ErrorLog.Txt on your local drive. You can use variables or expressions to make it work all dynamically.

:<)

Related Post: Read a flat file to a variable.

Here is my Twitter feed (@SamuelVanga) if you would like to follow me.

No matter how simple it is, new things come with some confusion; at least until you get used to it. Analysis Services 2012 Tabular is no different. This post will look at steps to create your first Tabular project.

Let’s start with firing up SQL Server Data Tools located under Microsoft SQL Server 2012 RC0. I’ve selected to create a new project.

In the above image, I’ve selected Analysis Services Tabular Project.

You might see a error message similar to the one below. This happens when the server instance specified in the Workspace Server property doesn’t have an analysis services running in tabular mode. For instance, in this example the name of my instance is localhost\SQL110, but I purposefully changed it to an incorrect name to show this error.

To fix this, I’ve changed Workspace Server to the correct one from the properties of model.bim

Importing Data

The Model option located on the top allows you to create model files. You’ll begin with importing data from a source.

The next screen lets you select a type of data source. I’ve selected Microsoft SQL Server. In the next screen, you choose a server name and database name as the source.

You’ll provide impersonation information in the next screen. This is important because, analysis services uses this account to connect to the data source. So, make sure that this account has required permissions on both the server instances: data source and analysis services.

Next screen asks how you want to import the data. You can either choose from a list of tables or view, or specify a query to retrieve the data. I’ve opted to select from a list.

From the list of tables and views in the below image, I’ve selected FactInternetSales, DimProduct, DimProductSubCategory, DimProductCategory, DimCustomer, and DimGeography.

Clicking finish will complete by the data import and the next screen displays a status of the tables and views that were imported.

Toggle to the diagram view to see the tables and relationships among the objects in the model. In the below image, you’ll notice the wizard identifies the relationships in the data source.

Toggle to the grid view to see the data that was imported from data source to the model. This interface looks similar or Excel workbooks with one tab for each table in the data source.

Create Measures

The first thing you should do after importing data is to create measures. I’ll select Sales Amount and choose Sum from the measure drop down in the following image.

I’ve repeated the same steps for Tax Amount, to create another measure.

Deploy To Target Server

Before deploying to the server, take a few seconds to verify the target server properties. Target Analysis Services must be running in tabular mode.

In the below image, I’ve selected to deploy the model.

Browse Your First Tabular Model

I selected Analyze in Excel option located on the top. An excel pivot table with a connection to the model you just deployed allows you to analyze data in Excel.

Conclusion

Well, Well, Well. I know this has been a lengthy post. So I’ll let you go with no further ado, but please use the comments below should you have any questions or comments.

~Sam