Tuesday, July 28, 2015

Best practices in SSIS Logging


  • Log always using SSIS logging mechanism
  • Do not reinvent logging mechanism using any manual methods even in C# script tasks
    • Script task to log into SSIS logging stream
      • http://dataqueen.unlimitedviz.com/2013/09/how-to-enable-custom-logging-for-an-ssis-script-task/
      • https://msdn.microsoft.com/en-us/library/ms136131%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
  • Inject the log destination from invoking platform. This will help to correlate the SSIS logs with other application logs.
    • eg: If the SSIS is invoked from C# application let the C# provide the logging connection string.
  • Better log all the events and point to database as logging store.
  • Have periodic routine to cleanup the logs.

Above are the best practices as of my understanding and experience with SSIS. Sorry to say that, I cannot guarantee that it will work for all.

Tuesday, July 21, 2015

Do we need SQL Server Integration Services running to execute SSIS / .dtsx package?

Question - Do we need a SSIS windows service to execute .dtsx file?

Here is the scenario. We have a .dtsx package file and need to execute it. Should we need to have SQL Server Integration Services running in our machine to execute that package?

Answer - No

Lets look at what is SQL Server Integration Services. By definition

"The SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages" 
From Microsoft KB https://support.microsoft.com/en-us/kb/942176 & MSDN Page

It clearly says we don't need need SSIS running in our machine to execute a package. We simply need the DTExec.exe utility to run .dtsx packages. So what is the use of SSIS windows service?

It is just to keep the .dtsx packages safe, initiate, monitor and stop the execution. It really means that there can be different users and roles who can only execute packages. Also helps to control the packages in remote machine.

Question - Do we need SQL Server running to run integration services package(.dtsx)

The SSIS is related with SQL Server. So do we need to have any SQL Server instance running, if we want to run a .dtsx package?

Answer - No

Though the Integration Services is for data transformation, it is not developed with tight integration with SQL Server. We can develop any algorithm using integration services. As seen above DTExec.exe is the execution utility. It doesn't need any SQL Server instance running to execute dtsx package is the package is not establishing connection with SQL Server.

What is the confusion?

Why people often confuse .dtsx package with SQL Server? The reason is mainly everybody thinks that since Integration Services are for ETL and data related operations, it must be linked with SQL Server and SSIS. Another reason is the expansion of SSIS starts with SQL Server. The better way is to start calling it as simply "Integration Services". As of my understanding even MSFT has the confusion. Some places they use simply Integration Services, some places Microsoft Integration Services and sometime refer full SQL Server Integration Services. So we cannot blame anybody for getting confused :)

So lets consider this as another technology to achieve workflow execution platform recommended for building enterprise-level data integration and data transformations solutions. This technology has many tools and components to help us in various phases of development and deployment. 

BIDS - Business Intelligence Development Studio

The tool which is used to create the workflow using drag and drop mechanism. It is Visual Studio itself. It gives option to define parameters, variables, connections, log provider and edit the script tasks. It captures the workflow steps in the form of xml and saved as simple file with extension .dtsx.

.dtsx

The file extension of file which contains the workflow in xml. This format is open and XSD can be found in MSDN. This file can be imported to SSIS package store to have better management. Integration Services .(dtsx) files can be created either from BIDS or using our own program. 

DTExec.exe

Utility used to to execute any .dtsx package either stored in file system or SSIS package storage. Parameters are passed via command line. Location
[Drive]:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

DTExecUI.exe

GUI utility to run .dtsx packages. Provides UI to enter the package parameters. Located at
[Drive]:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\DTExecUI.exe

SSIS

A windows service used to store and track execution of Integration Service packages (.dtsx)

Microsoft.SqlServer.ManagedDTS.dll

This is the .Net managed dll which provides API to create and execute .dtsx packages. This contains the namespace Microsoft.SqlServer.Dts.Runtime which holds the relevant classes. Located in below location if 2012 is installed

[Drive]:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

References

https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
http://stackoverflow.com/questions/454802/what-is-the-ssis-package-and-what-does-it-do

Tuesday, July 14, 2015

Not abel to create DTS.LogProviderTextFile.2 when running SSIS from .Net

Recently I was trying to execute a SSIS package from C# .Net code and it was not working as expected. So I decided to get the logs from the package. Unfortunately there was no logs. So I decided to inject the LogProvider and ConnectionManager from my C# code during execution.

Google lead me to a MSDN link which explains the same.
https://msdn.microsoft.com/en-us/library/ms136023(v=sql.105).aspx.

I selected SQL Server 2012 to get more accurate code snippet. The BIDS version I used was VS2010 based.

But I got below exception when I run the sample code.

A first chance exception of type 'Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException' occurred in Microsoft.SqlServer.ManagedDTS.dll

Additional information: The log provider type "DTS.LogProviderTextFile.2, {0A039101-ACC1-4E06-943F-279948323883}" specified for log provider "{652479A1-923C-452D-9328-5F703624B5FC}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.

Googled again and was not able to get much help. So decided to debug myself. I added LogProvider from BIDS UI and checked its type in Visual Studio before executing the package. It says the type is "DTS.LogProviderTextFile.3". It clearly indicate that if we create log provider with .3 suffix, it will work. I tried and it succeeded.

So if the target SSIS run time is SQL 2012 change the provider type to DTS.LogProviderTextFile.3. 

Tuesday, July 7, 2015

Developing Electron HTML5-Javascript desktop application in Windows 7

What is Electron?

The official repository says "Build cross platform desktop apps with web technologies". Its really true but it comes with some limitations. We cannot write system programs or device drivers with this. Its a wrapper over Chromium with native pluggable points to provide a platform for rendering HTML and running Javascript inside executable. Yes its true that we can develop cross platform windows desktop application using HTML and Javascipt. Similar to creating windows 8 or universal apps using HTML5 and Javascript. Advantage with Electron is, it can run in different operations systems such as Linux, OS X along with Windows.

Just look at official repository to understand this "our own mini chromium browser which can render our html javascript application".

https://github.com/atom/electron/blob/master/docs/tutorial/quick-start.md

Steps to setup environment

We can setup in multiple ways. The easier way I found is given below
  1. Go to the release page in Eletron Github repository.
  2. Download the latest platform version. At the time of writing this post the version is 0.28.3 and file size is around 46 MB.
    1. Take the 64 bit version if you have the 64 bit machine. The format is electron-vx.xx.xx-win32-x64.zip .Eg electron-v0.28.3-win32-x64.zip
  3. Extract to c:\Electron\ . It can be to any folder but just for simplicity put in c: drive itself.
  4. Double click on C:\Electron\electron.exe file to test the Electron runtime. We can see a default application has started. The source code for this application will be found in C:\Electron\resources\default_app

Steps to create new project

  1. Create a folder for our app anywhere in the file system. In Electron desktop app is similar to web app which is a folder of html, javascript and other resource files. For ease of explanation we are assume that the path is c:\apps\testelectron
  2. Copy package.json, index.html, and main.js from C:\Electron\resources\default_app into c:\apps\testelectron. This will get us the starting point similar to File->New ->Project in Visual Studio
  3. Clean up index.html
    1. Remove all styles and scripts
    2. Change the title tag content to "My First Electron Application"
    3. Make sure body contains only the word "Hello world"
  4. Replaces the contents in the main.js with the main.js contents from Github help page 
  5. Now run the project by any of the following methods
    1. In command prompt type c:\Electron\Electron.exe c:\apps\testelectron
    2. Start Electron.exe by double clicking and drag and drop the testelectron folder into the window.

Hide F12 Developer tools in Electron application

Debugging Electron apps

As we are seeing we gets our favorite F12 dev tools to debug our app. By default the developer tools will be visible when we run the application. To hide that remove the below line from main.js.

mainWindow.openDevTools();

Avoid default Electron menu on our application

Now our application is running from Electron shell. The Shell shows the menu. We may check this by looking at the C:\Electron\resources\default_app. To remove that we need to make sure our app is the only one started. To do so copy testelectron folder into C:\Electron\resources and rename testelectron folder to app.

Some magic happens and now if we double click on Electron.exe our application will start alone :). As you know computer cannot do magic. Its all convention defined by developers to speed up the process.

Packaging and distributing

Just refer the official documentation on packaging and distribution.

Pros

  • One language and technology for web and windows. This helps the developers focus on the business goal rather than spending time on google to learn technology and language.
    • Can leverage javascript frameworks such as angular, jQuery etc...
  • Leverage async programming model from node.js modules.
  • Easy portability. No need to maintain different code bases.
  • Comes with Chromium's process model. Each window is different process. This reduces chances to crash if we build big apps. Also minimize the memory leak as the process itself gone when a window is closed.
  • Package management done via Node Package Manager (NPM)

Cons

  • Its heavy. Need to package runtime with our app.
  • Not all native operations can be done.
  • As always need to use only The Good Parts of javascript
  • Process model put overhead on window communication. Need to use IPC. Also it takes more total memory than single process model.
As of Microsoft, they use this technology in their new VSCode code editor. Similarly many big players are adopting it. Its worth taking a look.