Monday, March 30, 2015

Replacing the Doc xPress Logo

One question we get a lot lately at Pragmatic Works is 'How do I change the Doc xPress logo at the bottom of my documentation to my own company's logo?'. That is a capability that is not too obvious, but it is there. The documentation that is created by Doc xPress is controled by a series of templates. In order to replace or add an image, it requires the changing of a property in the templates.

First, locate the templates folder. You will find it in C:\Program Files (x86)\Pragmatic Works\Pragmatic Workbench. The folder that contains the templates is named 'DOC xPress Templates'. Inside of that template folder, you will find another folder named 'Images'. This folder contains all the image files that are used when creating a document. Copy your desired logo image to this folder.

Next, you will have to locate all the templates for the root level objects. The templates that need to be edited are as follows...

SqlServer.template
SSASServer.template
SSISPackage.template
SsrsFileSystem.template
SsrsReport.template
SsrsWebService.template

In each one of these, the reference to the Doc xPress banner will need to be replaced. 

Note: Check the properties of each template. Chances are they are set to read only. If so, just uncheck the option and click 'Apply'. Also, run your text editor as admin.

I normally use Notepad++ for all my editing needs. In the image below, notice the last line of the template file.


Yes. This is a real config file. We're quirky here.







The image value needs to be changed. Just change it to the file name of the logo that needs to be displayed. I am switching mine to the SQL Goth logo. 











Save the file and close it. Repeat this with all of the template files listed above. Now, launch Doc xPress and generate a document. You should see your custom logo displayed at the bottom of the page.


If you're not using Doc xPress, you really should look into it. It will fulfill all of your documentation needs. Get more information here.

Monday, March 16, 2015

SSIS Performance Tip: Avoid the Sort Transform

Most BI developers are familiar with the concept of blocking and non-blocking transforms and why you should avoid blocking transforms. However, it does surprise me how many times I see people using the sort transform in their SSIS packages. The sort transform is fully blocking, meaning it needs to receive all the rows of data before it can do its job. This makes it one of the worst transforms to use. Using a TSQL command in the source and a few other options, you can smite the evil sort transform and prevent bottlenecks.

Take this OLE DB Source for example. I am using a SQL statement to select columns from a table that contains world mortality stats. Be sure to include an 'Order by' clause at the end. This one is ordering by the [Year] column.















Once you have your SQL statement, click 'OK' to close the source editor. Now, right click the OLE DB Source and select 'Show Advanced Editor'. In the advanced editor, click the 'Input and Output Properties' tab. In that window. click OLE DB Source Output and to the right you will see all of the output properties. Look for the 'IsSorted' property and set it to 'True' in the drop down menu.














Next, expand the 'OLE DB Source Output' node and then expand the 'Output Columns' node.



















In the list of output columns, find and select the column on which you would like to sort. To the right, you will see a list of properties of the column. Look for the property 'SortKeyPosition' and set it to a value of 1.

















If you are sorting on multiple columns, be sure to set that same 'SortKeyPosition' property for each one, making sure you select the appropriate number for the order in which they should be sorted.

The end result is the data being sorted as it is being retrieved from the source, through the power of TSQL. It's a huge time saver and one of the best techniques you can incorporate in your package design.

Thursday, March 12, 2015

Visual Studio Version Error

Here's a quick fix for a very annoying little problem. Every once in a while, when I attempt to execute a package in SSDT, I receive an error message that seems to relate to the version of Visual Studio 2012 I have installed.







If you need to copy this error message, it reads:

Method not found: 'Boolean Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()'.

I believe this happens almost every time I install updates to Visual Studio plugins or updates Visual Studio itself. The fix is quick and easy, but it is annoying to have to do it every so often.

First, make sure you close SSDT and open the Developer Command Prompt for Visual Studio 2012 as an Administrator. You can find this in the Windows start menu (or start screen, depending on what version of Windows you are using).














In the command prompt, navigate to the private assemblies folder for Visual Studio by executing the following command.

cd "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies"

Next, execute the following command to register the DTS.Design.dll.

gacutil /if Microsoft.SqlServer.Dts.Design.dll

Once that is complete, start SSDT and you should be able to execute SSIS packages again.

Friday, March 6, 2015

Running SQL Scripts Through Command Line

Let me start off by saying I am mainly an SSIS kind of guy. I don't normally find myself running SQL scripts inside of management studio. Today, I was putting together some sample databases so that I had something different to destroy other than AdventureWorks. I located a dataset that consisted of hundreds of thousands of books, ratings, and locations of users that rated the books. The download consisted of three SQL scripts that created the tables and inserted the data. Easy enough. Well, when I opened the first script, I realized that the script wasn't written for MS SQL. No big deal; I just had to make some minor adjustments. When I ran the first script, I was presented with this lovely error...



I haven't had this happen before, but I have never tried to load over half a million rows of data with a SQL script either. This is when I must turn to the power of the SQL command line. Using the command line to run SQL scripts uses a lot less memory. Like most applications that have command line options, it also runs a lot faster.

First, open up a command prompt. There are lots of different parameters you can use with the command line tool. In order see a complete list, use the following command.

SQLCMD -?

Since I am doing this on my local instance, the command is rather small and straightforward. When running the command against a default instance on a local server, all you have to do is point to the target database. Also, since I have the scripts saved to a file, I have to specify the location of those files. To do this, I will use the parameters '-d' and '-i' respectively. The command prompt should look like the following screenshot.


I am pointing to the database named 'BookRankings' and running a script that is stored in a folder on my desktop. Running this command took less than a minute. After it was completed, I ran a select statement against the table just to make sure, and as you can see from the image below, everything loaded just fine. 


So remember, if you ever have to run an insanely long SQL script that SSMS just can't handle, use SQLCMD. It's fast, convenient, and the black, abysmal environment of the command prompt is kind of like working in the dark,which is where you will find me.