WMI implementation in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server
What is WMI? WMI stands for Windows Management Instrumentation. WMI is in query scripting language that is capable of managing your windows server and workstation. SSIS task for reading information in your workstation or server is provided by WMI data reader. For example to look information of your application log.
With WMI data reader, you can :
1. Read event log
2. Query list of running application
3. Query memory available for executing your SSIS package.
4. Get information of hard disk free space.
For example to get information of free disk space on c drive, you issue : SELECT FreeSpace, DeviceId, Size, SystemName, Description FROM Win32_LogicalDisk WHERE DeviceID = 'C:' or you want to get information about SSIS service and package running you issue : SELECT * FROM Win32_NTLogEvent WHERE LogFile = 'Application' AND (SourceName='SQLISService' OR SourceName='SQLISPackage').
WMI data reader task need WMI type connection manager. On task editor of the task, you specify whether you specify your query directly or through file/variable in WsqlQuerySourceType and specify the query itself in WsqlQueryType, and what kind of output format you would like to get, tabular format or property name – value format or just value format at Output type property. Destination type property gives you options whether you would like to save the output into file or variable, if you want to see the result directly without further processing, you can select the output to file.
Run your package, WMI reader task will read information through WMI query you specify.

Interacting SSIS with Web Service

By: Kasim Wirama, MCDBA, MVP SQL Server
Other interesting component of SSIS is web service component as interface to interact with other third party through web service.
You can build your own web service with Visual Studio, then you can create your SSIS project by first creating HTTP connection manager. Specify server url in HTTP connection manager editor window. Then you drag web service task into control flow designer, on general page, select http connection manager on HttpConnection property, type wsdl file for saving into your local SSIS, and specify whether you would like to override the wsdl file.
On input page, you can select service name and web service method. And on output page you can receive web service output to any destination such as to variable or to file.
You can test your SSIS package, when check that the output will be saved into your specified destination.

What are new features offered in SQL Server 2008 ?

By : Kasim Wirama, MCDBA, MVP SQL Server
SQL Server 2008, code name Katmai, will be officially released on 27 February 2008. There are a number of changes that will impact on database design and some performance gained with new feature. Some aspects will need you consideration below :
1. Consider to use latest hardware, such as dual core or quad core processor, and 64 bit machine that has maximum memory 1 TB instead of 32 bit that has maximum 4 GB memory. And use 64 bit SQL Server 2008
2. Consider to use 64 bit latest server operating system (windows 2003 server SP 2 or windows 2003 R2). Those OSes will support SQL Server in high load utilization. If you install Scalable Network Pack for TCP Offload engine, it will free up CPU cycle. If you use Windows Server 2008, it will have capability to send dynamic frame sizing on TCP/IP low level.
3. Database mirroring on SQL Server 2008 will be much better compared to SQL Server 2005. It will be easy to implement and will compress log stream over network, resulting efficient bandwidth utilization, that will impact on system performance overall.
4. SQL Server 2008 will use data compression on LOB data type such as IMAGE and TEXT, less physical I/O has to be done, improving performance.
5. Data compression will also be applied to backup so it will reduce storage requirement.
6. Regard to store LOB data into database, SQL Server 2008 will introduce new data type FILESTREAM to address performance overhead imposed on store LOB into database. LOB will be keep externally and FILESTREAM will give high performance accessing the LOB outside database because it integrates will Windows file system whilst still maintaining database integrity.
7. SQL Server 2008 introduce some datetime datatype, namely DATE, TIME, DATETIMEOFFSET (datetime datatype with timezone) and DATETIME2 (larger year and fractional second range). It will make datetime related data migration easier from other database platform such as Oracle and DB2.
8. If you write some queries that involve some grouping with refers to same table, you can just write only one query with GROUPING SETS new TSQL feature of SQL Server 2008. It is simpler and fewer scan on base table, resulting better performance.

Building First Windows Communication Foundation

By : Kasim Wirama, MCDBA, MVP SQL Server
Hey, .NET 3.5 will come soon, and I haven’t got know anything about WCF. So I learn how to build service in WCF so it could be consume by clients. So this application contains 2 parts, service as server side and client that consumes the service. I will host the service into IIS.
At glance, WCF is a framework that provides communication mechanisms that involve client from windows and non windows world. Of course there are some aspects that should be taken into account for this service run reliable, scalable and secure to be accessed. I leave additional theory behind this WCF to leading leading MVP C# to explain more about this kind of “animal”.
So you install first your WCF, and Visual Studio extension for WCF and WPF, there is database sample used, AdventureWorks. I use enterprise library May 2007 build.
Make new project in VS2005, there is option NET Framework 3.0 in Visual C#’s or Visual Basic tree node of Project types list box, choose WCF Service Library. Name it ProductServiceProject. Configure project property Output Path from bin\debug to bin\ in Build section, make note to your assembly name and default namespace.
I also add reference Microsoft enterprise library for data.dll, common.dll, and object builder.dll
There is one class created by default, you just add corresponding Microsoft enterprise library namespace in “using (for C#)/Imports (for VB.NET)” section.
There must be 2 namespace that included in every class in WCF projects, “using System.ServiceModel” and “using System.Runtime.Serialization”. “using System.ServiceModel” contains all WCF related classes and “using System.Runtime.Serialization” contains all classes required to serialize object over wire and deserialize into object again.
Now at sample section you will see 2 attributes, ServiceContract and DataContract. DataContract identifies a class/enumeration/structure that can be serialized/deserialized as XML stream by WCF. ServiceContract identifies interface as a contract, so clients application would be able to generate metadata to consume the WCF service.
A class contains members, you can mark each members that you would like to serialize/deserialize with DataMember attribute. And for each member of interface that is marked as OperationContract, could be exposed with OperationContract interface. And you create a class to implement the interface. Let’s see below sample script to explain about this:
[DataContract]
public class Product
{
[DataMember]
public string ProductNumber;
}


[ServiceContract]
public interface IProductService
{
[OperationContract]
List ListProducts();
}

public class ProductServiceImpl : IProductService
{
public List ListProducts()
{
Database dbAdventureWorks = DatabaseFactory.CreateDatabase("AdventureWorksConnection");

string queryString = @"SELECT ProductNumber
FROM Production.Product";

IDataReader productReader = dbAdventureWorks.ExecuteReader(CommandType.Text, queryString);

List productsList = new List();

while (productReader.Read())
{
string productNumber = productReader.GetString(0);
productsList.Add(productNumber);
}
return productsList;
}
}

If you want to deploy it to IIS, you need to specify the class that implement the interface and assembly that contains the class in separate service file (SVC file) as below script.
<%@ServiceHost Service="Products.ProductServiceImpl" %>
<%@Assembly Name="ProductsService" %>
In web config add below scripts in section



binding="basicHttpBinding"
contract="Products.IProductsService" />



Service Model element contains configuration information about WCF web service.
Services element contains detail implementations for each service.
Endpoint element contains detail of each services that client application will require in order to communicate with the corresponding service.
Address attribute is left blank because IIS will use SVC file.
Binding element provides transport mechanism to access the WCF web service.

Next step is to make virtual directory point to your WCF project. After deployment you can see your service by typing http://localhost//. Notice that there is sentence “Metadata publishing for this service is currently disabled”. To make it enable to add this script in webconfig inside







And add new attribute inside service element with behaviorConfiguration="ProductsBehavior".
I have done for WCF service part. I try for WCF client part that consume the service. 

Scripting in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

For most business requirement scenarios, use of click, drop and select property of some tasks is sufficient. But the other scenarios, you need to provide custom solution that is not provided by the tasks (such as File System task or FTP task).

In DTS, you have only one scripting option, using ActiveX Script task, but in SSIS, you have other options, using .NET scripting provided by Script Task, besides ActiveX Script task. Let’s see what’s inside ActiveX Script task.

When you open Script section at ActiveX Script Task Editor window, there are some properties, such as Language, Script and EntryMethod. Still same with SSIS predecessor, there are VBScript and Jscript language. At Script property, you can type your script here, or alternatively, you load your activeX script file from Browse button, remember that if you load from file, all script that you type in Script property will be overridden. And you specify EntryMethod that will be first executed in script. Nothing new in this task, I recommend you move to Script task rather than ActiveX script task, for several advantages:
1. Intellisense with Visual Studio environment
2. can pass parameter into script
3. can add breakpoint to your script
4. Managed in .NET environment
5. You can precompile your script for faster execution performance.

Now, explore what Script Task component look like. In Script property, there are some properties.

ScriptLanguage property currently contains only Visual Basic .NET, but in next version of SQL Server 2008, it will have options Visual C# also.

PrecompileScriptIntoBinaryCode property is set to true, will make execution faster but file size will be bigger.

EntryPoint specify entry point method, just leave it as default.

ReadOnlyVariables contains variables that are read only purpose, and WriteOnlyVariables contains variables that can be read-write purpose.

You can try it out, first make 2 variables, named it readVar and writeVar with string type, you expected when readVar is assigned to “Hello” will output to writeVar as “Hello world”. Set value “Hello” to readVar, then define in Script Task Editor, ReadOnlyVariables to readVar and ReadWriteVariables to writeVar. Click Script button and it will open another visual studio window called VSA (Visual Studio for Application). In VSA, type dts.Variables(“writeVar”) = dts.Variables(“readVar”) & “ world” and Msgbox (dts.Variables(“writeVar”).values.ToString()) before last statement (TaskResult) and procedure Main.

Now, you run the package, it will display pop up message with text “Hello World”
Script task is very useful and offer flexibility and familiar to use when you are already used to working with Visual Studio.

Sending Mail in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

In most business requirement, business user would like to be notified about an execution status of SSIS package, through number of ways, such as logging. Another preferred way is by sending email to related user. SSIS provides this through Send Mail task. Scenario example for using this task when you would like to be notified by email when backup database task is failed or sales target has not reached minimum sales amount.

This task requires SMTP task connection manager. So you need to specify SMTP based connection manager before using the task. On the connection manager, you specify smtp server name.

On this SSIS version task, it has no longer dependency with MAPI. On DTS, it has dependency with MAPI, means that you should install Outlook on machine where the package will run.

Let see some properties for the task here:
SmtpConnection property contains name of connection manager you specified earlier. Another interesting property is MessageSourceType, there are 3 options, Direct Input, File Connection, and Variable, so it gives you more flexibility depending on business requirement. MessageSource will depend selection you make in MessageSourceType. As email priority, this task provide email priority through Priority property. And you can attach file through Attachments property. You can specify multiple attachments separated with semicolons. Unfortunately here, there is no way to define attachment property through variables.

Message Queuing Implementation in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

SSIS provides tasks that can communicate with other application through windows built in messaging, called Message Queue (MSMQ). The task is Message Queue task. It is used to interact with message queue service. A message can contain file, string or variable. With this task, you can scale up your package, enabling multiple packages sending asynchronous messages to other application. Let’s see what configuration inside the task.

In General page, you specify message queue connection manager. On Message Option property, you specify whether the task is configured to send or receive message. Other property is whether to use windows 2000 MSMQ.

If you choose send message, the Send option will appear on left list box. On Message Type property, you can set what kind of content will be in message, the content could be variable, file or string type.

If you choose receive message, a bit more property will be there, such as time-out property and remove message from queue after read it. For time out property, if it is specified, it will produce error if it experiences a time out.

It is interesting task, I think. If you would like your application to interact one another through loosely coupled, tool or component interact with MSMQ is worth to be considered for use.

Simplifying File Operation in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

In previous version of SSIS (i.e.: DTS), if you want to do file or directory related operation, you have to write this in ActiveX script task. For one or two operation, it would be no problem to maintain, but imagine when you ETL process involves many file or directory operation, you will need some efforts to understand and modify the ActiveX scripts. So it is not practical anymore in this situation.

SSIS provide File System Task component to simplify these processes by just selecting appropriate already built-in functions inside the task, such as Copy Directory, Copy File, Create Directory, Delete Directory, Delete Directory Content, Delete File, Move Directory, Rename File, and Set Attributes.

Let’s say if you want to Move Directory along with their files inside, some properties that should be specified below, based on category : Source Connection and Destination Connection.

For Source Connection property group, there are 2 properties, if you would like to assign value from variable to SourceConnection property, set it true on IsSourcePathVariable, then you can select drop down values on SourceVariable property.

For Destination Connection property group, there are 3 properties, similar to Source Connection property group, there are properties IsDestinationPathVariable and DestinationConnection. If you want to override existing file or directory and destination when file/directory name similar with source, you can set the value to true for Overwrite Destination property.

With complete function of the task, you will remove necessary to write complex ActiveX script and the package becomes easy to maintain.

Modularize SSIS Package

By : Kasim Wirama, MCDBA, MVP SQL Server


Sometimes, your package is too big and some of the tasks are reusable. You can break your big package down into smaller packages so that you can reuse your smaller package in other SSIS projects. SSIS supports this requirement by provide Execute Package task. Let’s examine more detail what configuration that the task offers.

The important configuration is located on Package section on list box of Execute Package Task Editor window, some properties are important to get familiarize with.

First is Location properties, you need to know where your package is save to, in SQL Server or in File System. When it is in SQL Server, the package is stored into msdb database. If it is in File System, you need to specify where it is located in file system.

Specify password property, when the called package is in encrypted form for security purpose.

Other property is ExecuteOutOfProcess. By default the value is false. When it is true, the referenced package will be executed in separate process and memory space.

How about setting up input parameters on child package. In DTS, you need the re-supply them to the child packages, but in SSIS, it is not necessary, because the child package will read input parameters through configuration of main package.

It is good practice if you break your large package into smaller ones, there is issue of memory (message : OutOfMemory exception) when you develop your SSIS package too big (let’s more than 10 MB). So to address this issue, the workaround is break the big SSIS package into smaller ones, and get arranged by main SSIS package, the Execute Package Task comes into play for this scenario.

File Transfer Activity in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

Task for file activities through FTP (File Transfer Protocol) has been introduced in DTS. With major improvement in SSIS, the task has even more functionality than ever before.

What is difference functionality between the task in DTS and SSIS? In DTS, you can only receive file through FTP, but in SSIS, not only you can receive, but also you can send, delete local or remote file. Here is complete function available in FTP task on SSIS : send/receive file, create/remove local/remote directory, delete local/remote files.

The task need connection to FTP type, so you need to create FTP type connection manager. In FTP Connection Manager Editor window, you specify FTP server name, by default FTP port is 21, but if your FTP is secure FTP, the port is usually 22. If your FTP requires any connection get authenticated before doing any file operation, specify it in UserName and Password in Credentials section. If you FTP uses passive mode, check it on Options section.

After you define FTP connection manager, you can create FTP task in Control Flow Designer. In FTP Task Editor window, select the FTP-based connection manager on FTPConnection property.

On File Transfer section, you will see similar properties with those of File System Tasks. For Local Parameters property group in FTP is similar to Source property group in File System task. And For Remote Parameters property group in FTP is similar to Destination property group in File System task. One unique property that attracts my attention is IsTransferAscii property, by default the value is false. Most of FTP clients the default is true, if your FTP file operates in Ascii transfer mode, adjust it to avoid content inconsistency when your files get transferred between local and remote connection.

With SSIS, Your package has much of FTP functionalities in file/directory operations ever than before.

Precedence Constraint in SSIS

Precedence Constraint in SSIS
By : Kasim Wirama, MCDBA, MVP SQL Server

If you ever used earlier version of SSIS, DTS, you usually use precedence constraint to connect from one task to another task, there are 3 kinds of precedence constraints, success, failure and completion to enable next task get executed. An improvement made by SSIS for this constraint.

There is addition besides constraint value (success, failure and completion), also another functionality that can evaluate some value dynamically before allowing next task get executed. The functionality is possible through conditional expression on precedence constraint. And you can combine between precedence constraint value and precedence constraint expression with OR/AND operator.

For example, if you want ExecuteSQLTask for executing send mail task if ExecuteSQL task has failed executed or no records returned from ExecuteSQL Task, you can configure precedence constraint to precedence constraint value OR precedence constraint expression. So there are 4 options for you based on business requirement :
1. Constraint
It is default setting, the value is success, failure, or completion
2. Expression
This is where you specify condition that should be satisfied before move to next task
3. ExpressionAndConstraint
The combination that must fulfil constraint made by both Constraint and Expression
4. ExpressionOrConstraint
The combination that should fulfil constraint made either by Contraint or by Expression.

I think this functionality makes package flow behaviour get executed more dynamically.

Calling Other Executable in SSIS

Calling Other Executable in SSIS
By : Kasim Wirama, MCDBA, MVP SQL Server

Sometimes, you need to call other executable through SSIS, let say, you write custom business logic in executable, and you need SSIS to call the executable (out of process), bottom line here that you want the module is reusable without rewrite same business logic in SSIS. With the component that wraps the executable inside SSIS, you just focus on the executable, so easy and efficient results you can get with the component.

The component is called ExecuteProcessTask. The component in SSIS is enhanced compared to previous version of this in DTS. You can write error information to a variable and you can redirect output from the command file to a variable.

Some variable here you should to configure here.
RequireFullFileName property should be set to true if you require your package to know exactly the location of the executable.

Executable property should specify executable name, for example notepad.exe, it is better if you specify fullfilename.

Arguments property contains list of arguments that you want to pass into Executable property.

Working Directory property contains input for location from which you executable is executed from.

StandardInputVariable property contains values as input arguments for the executable.

StandardOutputVariable property will keep execution output into variable.

StandardErrorVariable property will keep execution error information into variable.

If you set true on FailTaskIfReturnCodeIsNotSuccessValue property, it will see value on SuccessValue property, if return value is same with value of SuccessValue property defined, so it is considered success, otherwise failed.

Timeout property defines in seconds how long the executable is expected to finish execution. Value 0 means wait indefinitely. This property works with TerminateProcessAfterTimeOut property.

WindowStyle property contains value Normal, Maximized, Minimized and Hidden. If you would like to avoid interaction the executable to end user, set it Hidden.