Types of connections in SSIS

Today we will discuss about various connections available in SSIS. First of all we will understand what connection is.

A connection is a communication between resources by which they can communicate with each other. In SSIS you can perform different tasks with connection such as

• Connect to relational data base to execute SQL commands or SQL stored procedures
• Connect to Excel sheet and fetch data
• Connect to flat file to import data
• Connect to XML file to extract data from nodes.

Connection manager is logical representation of connection which is placed under Design editor as shown in Figure 1. The properties, which are used to create connection at design time, will be used at runtime to establish physical connection to execute the SSIS package.

Figure 1

To add new connection, Right click on connection manager and select new connection which takes us to ADD SSIS connection Manager Tab as shown in Figure 2 this tab contains all built-in connections.

Figure 2

ADO connection: ADO connection allows package to connect to ActiveX data objects(ADO) such as record set. This connection manager is typically used in custom tasks written in an earlier version of language.

ADO.NET connection: ADO.NET connection enables package to connect data sources by using .NET provider. 

CACHE connection: Cache connection enables package to read data from cache file(.caw) and write data into cache file. The data is always saved in memory whether you can configure the cache connection manager.

EXCEL Connection: Excel connection allows package to connect existing excel work sheet to read data or write data.

FILE Connection: File connection allows package to connect existing file or folder or create file or folder during runtime. File can be may be either XML or excel file.

FLATFILE Connection: Flatfile connection allows package to connect to single flat file to access data.

FTP Connection: FTP(File Transfer protocol) connection allows package to connect FTP server to move or delete local and remote files and create or delete local and remote directories.

HTTP Connection: HTTP connection allows package to connect web server for receiving or sending files. Web service task uses HTTP connection to connection web service to execute web methods.

MSMQ Connection: MSMQ connection allows package to connect to message queue that uses Message queuing(MSMQ). Message queue task in SSIS this connection.

MSOLAP100: MSOLAP100 is a analysis service connection manager. This connection manager allows package to connect to Analysis database server or analysis services project to access cube and dimension data.

MULTIFILE Connection: MultipleFile connection allows package to connect existing multiple files or folders or create multiple files or folders during runtime. File can be may be either XML or excel file

MULTIFLATFILE Connection: multiple flat file connection allows package to connect multiple flat file to access data. In SSIS you can use multiflatfile connection to connect data from different sources.

ODBC Connection: ODBC connection allows package to connect to open databases using open data base connectivity(ODBC) specification.

OLEDB Connection: OLEDB connection allows package to connect to data source by using OLEDB provider. An OLEDB connection manger that connects to SQL server can use Microsoft OLEDB provider for SQL server.

SMOServer connection: This connection allows package to connect SQL management object(SMO) server to perform SQL server managing tasks.

SMTP Connection: This connection allows package to connect SMTP(Simple Mail Transfer protocol) server to send mails. Send mail task is SSIS uses the connection

SQLMOBILE Connection: This connection allows package to connect SQL server compact database(.sdf)

WMI Connection: This connection allows package to use WMI(Windows management instrumentation) to connect server or work station by writing WQL queries. WMI data reader task and WMI event watcher tasks is SSIS uses WMI connection manager.

All the above mentioned connections will be created in SSIS as and when we are creating respective task.

Please leave comment if you like the post

Read more ...

XML task in SSIS with example

XML task in SSIS

In this article we will learn about XML task in SSIS. XML task is used to validate, modify, extract or even create files in an XML format.

Drag XML task from tool box and drop on Control flow tab as shown in Figure1

Figure 1

Double click xml task which displays XML task editor as shown in Figure 2 which is having only two tabs. In General tab, you need to specify the connections and type of operation should be performed.

Figure 2

General tab properties changed based on the OperationType selected. The XML operation types are as follows.

• Validate: This option allows XML file schema validation against Document Type Definition (DTD) or XML Schema Definition (XSD) binding control documents. XML task with this option makes sure that XML file is in required format.

• XSLT: The Extensible Stylesheet Language Transformations (XSLT) are a subset of the XML language that enables transformation of XML data.

• XPATH: This option uses the XML Path Language and allows the extraction of sections or specific nodes from the structure of the XML document. This option will be used to extract data from XML nodes.

• Merge: This option allows for the merging of two XML documents with the same structure. this option will be used to combine the results of two extracts from different systems into one document.

• Diff: This option allows us to compare two XML documents to produce a third document called an XML Diffgram that contains the differences between them.

• Patch: This option applies the results of a Diff operation to an XML document to create a new XML document.

You can specify the XML file source either through direct input or variable or file connection. This is common for all options.

Secondoperation tab specifies source type and source that contains second XML document to complete the selected operation.

Lets us understand XML task with an example which uses validates an XML using validate option

Steps to follow:

1. Create one dummy xml file as mentioned below. You can create using any available XML editor.
<?xml version="1.0" encoding="utf-16" ?>
<NewDataSet xmlns="" >
<table>
    <column1>col1</column1>
    <column2>col2</column2>
    <column3>col3</column3>
    <column4>col4</column4>
    <column5>col5</column5>
    <column6>col6</column6>
</table>
</NewDataSet>
2. Generate XSD/XML schema either through any generate tool or online. I have created below XSD document using online tool saved as text_xml.xsd
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="NewDataSet">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="table">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="column1"/>
              <xs:element type="xs:string" name="column2"/>
              <xs:element type="xs:string" name="column3"/>
              <xs:element type="xs:string" name="column4"/>
              <xs:element type="xs:string" name="column5"/>
              <xs:element type="xs:string" name="column6"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
3. Create a package and add XML task on the control flow design editor.
4. Open configuration configuration editor and select operation type as validate
5. Select a source type as direct input and Write the XML. You can specify even through file connection
6. In OperationResult property in Output tab specify how to capture the result of validation. Add output result to file test.txt and set OverWriteDestination property to true to allows result to be over written.
7. In SecondOperand tab you need to create file connection with XSD document which is created in Step2
8. Set validation type as XSD file to validate XML file.

XML task editor should be shown as displayed below after configuration completed.

If you execute the package, Task will be executed successfully and turned into Green color and writes true into text.txt file which indicates XML file contains the correct schema as defined by XSD file. If you want you can test giving wrong XML file which writes false into file

Please leave comment if you like the post

Read more ...

WMI Event Watcher Task in SSIS with example

We have discussed about WMI Data Reader task in previous article. In this article I will discuss about WMI Event Watcher Task in SSIS. WMI event watcher task gives ability to SSIS respond to certain WMI event.

The following are some useful things can be done with the task

• Wait for a specific file or directory to be processed in the control flow
• Wait for CPU to be free
• Wait for some services to start/stop to proceed
• Watch for installation of any application
• Watch for any specific error message to proceed.

Drag and drop WMI Event Watcher Task from tool box onto control flow design as shown in Figure 1

  Figure 1

Double click on the task which opens up configuration editor as shown in Figure 2

Figure 2

In General tab you have to specify name and description of the task. Next tab is WMI options tab where you have to specify required properties for the task as shown below Figure 3

Figure 3

Specify WMIConnection connection by selecting <New Connection…> option under WMIConnection which pops up WMI connection Manager editor as shown below Figure 4

  Figure 4.

• Name: provide a name for the connection manager.
• Description: Describe the connection manager.
• Server name:provide the name of the server to which you want to make the WMI connection.
• Namespace:Specify the WMI namespace.
• Use Windows authentication: Select to use Windows Authentication. If you use Windows Authentication, you do not need to provide a user name or password for the connection.
• User name and password should be provided if Windows authentication is not selected.
• Test Options allows you to verify connection.

For the purpose of example, WMI connection is created with stand-alone work station(Local host) server as shown in Figure 4. 

Root\Cimv2 is default namespace for writing WQL queries

Other options in WMI options tab are as follows.

• WqlQuerySourceType option specifies source type of WQL query which can either direct input or file connection or variable.
• WqlQuerySource option specifies source of WQL query. Source will be provided based up on source type specified.
• ActionAtEvent: Specifies the action to take when the event occurs
• AfterEvent: specifies the action to take after event
• ActionAtTimeOut:specifies action to take when the time out occurs.you will specify Time out value in time out property.
• AfterTimeOut: specifies the action to take after timeout.
• NumberOfEvents: Specifies the number of events that task should watch
• TimeOut: Specify time out in seconds.Default is 0 which mean there is no time out.

Example: Keeps on checking C:\SSIS directory for delivery of a file to proceed.

For the purpose of the example, Following query should be passed which gives notification that any file is added in the directory.

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\SSIS\""

The task WMI options tab should be displayed as shown below after configuration.

 

If you execute the task with no files in the C:\SSIS folder, it keeps on checking for the file and the task will be in Yellow color. Once you copy any file in the folder, task color turns into Green color and completed successfully.

NOTE: Please leave comment if you have any doubts

Read more ...

WMI Data Reader Task in SSIS with example

In This article i will discuss about WMI Data reader task. WMI(Windows management instrumentation) Data reader enables you to interface with server or work station by writing WQL. WQL is a WMI query language and is like T-SQL query language.The output of the query can be saved either in variable or file connection for later use.

Following are some applications for which you could use this task

• Read Event viewer log for any errors or warnings
• Reads size and other details of drive
• List out the application which are currently running on the server
• Getting RAM available space for package execution or debugging
• Gets list of applications and their current version installed

Drag and drop WMI Data Reader Task from tool box onto control flow design as shown in Figure 1

Figure 1

Double click on the task which opens up configuration editor as shown in Figure 2

Figure 2

In General tab you have to specify name and description of the task. Next tab is WMI options tab where you have to specify required properties for the task as shown below Figure 3

   Figure 3

Specify WMIConnection connection by selecting <New Connection…> option under WMIConnection which pops up WMI connection Manager editor as shown below Figure 4

Figure 4.

• Name: provide a name for the connection manager.
• Description: Describe the connection manager.
• Server name:provide the name of the server to which you want to make the WMI connection.
• Namespace:Specify the WMI namespace.
• Use Windows authentication: Select to use Windows Authentication. If you use Windows Authentication, you do not need to provide a user name or password for the connection.
• User name and password should be provided if Windows authentication is not selected.
• Test Options allows you to verify connection.

For the purpose of example, WMI connection is created with stand-alone work station(Local host) server as shown in Figure 4. 

Root\Cimv2 is default namespace for writing WQL queries

Other options in WMI options tab are as follows.

• WqlQuerySourceType option specifies source type of WQL query which can either direct input or file connection or variable.
• WqlQuerySource option specifies source of WQL query. Source will be provided based up on source type specified.
• OutputType: this option specifies whether you want output of query to retrieve just values or also column names with values
• OverWriteDestination: option specifies whether data in destination file or variables is kept,overwritten or appended.
• DestinationType: specifies destination type.
• Destination:Specifies the file connection or variable to save output of the task

For the purpose of example, The following query gets free space,DeviceID, Size of desk,System name about of C Drive.

SELECT FreeSpace, DeviceId, Size, SystemName, Description FROM Win32_LogicalDisk WHERE DeviceID = 'C:'

Once configuration completed WMI options tab should look like below figure 5

Figure 5

Execute the task.The output of the task will be saved in text.txt file as shown below

Description, Local Fixed Disk
DeviceID, C:
FreeSpace, 2424451072
Size, 36709388288
SystemName, INLTE5569

Run wbemtest.exe command which takes you to WMI tester Menu. Where you can write your WQL queries to test.

NOTE: Please leave comment if you the post

Read more ...

For Loop container in SSIS with example

Today in this article i will explain about one of the containers For Loop container. For Loop container groups set of tasks together and allows you to looping through the tasks in your package which is similar like for loop in any programming language.

• You can a send a mail for set of people.
• You can loop through an SQL statement and perform certain action until end of the table

Drag and drop For Loop container from tool box on to Control flow design editor as shown in Figure 1.

Figure 1.

Double click on For Loop container task which takes us to configuration editor as shown in Figure 2.

Figure 2

Configuration editor has only two options For Loop and Expressions.

InitExpression: This will initialize the loop with specified values

EvalExpression: specifies an expression to stop the loop when the expression evaluates to false. This expression must evaluate to a Boolean.

AssignExpression: specifies an expression that changes a condition in the same way each time the loop iterates.Which is optional one.

We will create a simple example which deletes one row from table product at time

Steps to Follow:

Step1: execute below queries which creates product table with id and name columns and insert 6 rows.

create table product(prodid int,name nvarchar(10))

insert into product values (1, 'prod1');
insert into product values (2, 'prod2');
insert into product values (3, 'prod3');
insert into product values (4, 'prod4');
insert into product values (5, 'prod5');
insert into product values (6, 'prod6');

Step2: Drag and drop For loop container and configure as shown below

Step3: Drag and drop Execute SQL task inside For Loop container as shown below. Here, Execute SQL task will be executed until EvalExpression condition fails. In this example Execute SQL task will be executed 6 times.

 Step4: Configure Execute SQL task in such a way it should delete one row based up on prodid which matches with @count variable from product table.

For the purpose this example, Connection property is specified with the AdventureWorks connection where Product table is created and SQLSourceType is specified as Direct input and SQLStatement is delete from product where prodid = ?. ? is a parameter and which will be replaced with @count variable during run time. @count variable will be attached to ? parameter under Parameter Mapping tab.After configuration is done task editor should reflect like below Figure.

Step5: Execute the Package which deletes all 6 rows from product table If configuration is properly set. Refer Below figures where are displayed after execution of package.

ControlFlow Design editor  

ProgressBar After execution

NOTE: Please leave comment if you like the post

Read more ...

Execute process task in SSIS

Today i will discuss about one of the work flow tasks execute process task(Refer tasks article to know about all tasks in SSIS).

Execute process task enables us to execute either window based or console based application inside control flow of the package and also can be used to open standard applications Microsoft word,Excel or notepad.

In Real world this task is used to unzip or decrypt file. Package then use the file as a data source for data flow task to load data

Drag and drop Execute process task from tool box on to control flow as shown in Figure 1

 Figure 1

Double click on the task which opens task editor which has only two tabs General and process. In General tab, name and description should be specified.

Next tab process contains all configuration properties required to execute process as shown in Figure 2.

Figure 2

RequireFullFileName: property specifies whether task should fail or not if executable path not found. Process task usually checks in system 32 path or PATH environment. You need to specify full path explicitly other than those paths

Executable: Property specifies executable file name that you want to run

Arguments property specify arguments for the executable. Arguments should not be specified in executable property

WorkingDirectory: contains the directory from which executable executes

StandardInputVariable specifies variable name if arguments are passed as variables for process

StandardOuptutVariable should specifies variable name to capture output as variable

StandardErrorVariable specifies variable names if error in the process should be captured in variable

FailTaskIfReturnCodeIsNotSuccessValue indicates whether task should fail if process exit code does not match with success code

SuccessValue specifies the value to indicate success code of the process task.

Timeout specifies Time out values measures in seconds

WindowStyle specifies the appearance of new executable window. Options in the tab are normal,minimized, maximized and hidden

We will understand above process by creating a package which unzips file(products.zip) using process task.

Steps to follow:

step1: create a text file(product.txt) with the below mentioned data and ZIP the file as products.zip and Products.zip file created in D:\dot net\SSIS folder

1,prod1
2,prod2
3,prod3
4,prod4
5,prod5

step2: Drag and drop Execute process task onto control flow task

Step3: Configure process tab with the below mentioned details.

Executable C:\Program Files\WinZip\WINZIP32.EXE

Arguments for winzip32 -e -o D:\dot net\SSIS\product.zip

Working directory D:\dot net\SSIS. All other properties need not to be specified as not using in this example.

Step4: Execute the package once configuration done which extracts file from products.zip and places product.txt in working directory(D:\dot net\SSIS)

Read more ...

FTP task in SSIS

In this article i will explain about FTP task in SSIS. FTP(File transfer protocol) task enables us to move or delete local and remote files and create or delete local and remote directories.

Following file transfer operations can be performed using FTP task

Send Files: send files from local server to FTP server

Receive Files: receive files from FTP server to local server

Create local Directory: creates directory on local server

Create remote Directory: creates directory on FTP server

Remove local Directory: removes directory on local server

Remove remote Directory: removes directory on FTP server

Delete local files: delete files on local server

Delete remote files: delete files on FTP server

Drag FTP task from tool box on to control flow design editor as shown in Figure 1

  Figure 1

Double click on FTP task which takes us to configuration editor as shown in Figure 2.

Figure 2:

In General tab, select <new connection…> under FTPConnection which opens FTP connection manager as shown in Figure 3 where you need to configure FTP connection.server name property contains FTP server address and set server port as 21 which is default for most FTP servers. You can change server port number if required and Credentials should be specified if FTP connection requires.

Time-out(in seconds) property indicates how much time package tries before task fails and select use passive mode option to enable passive mode.Retries property shows how many times package should try to connect FTP server and last option Chunk size(in KB) specifies the data transfer size.

  Figure 3

Click on Test connection button to verify whether FTP connection is established or not. Next options in General tab is StopOnFailure specifies whether task should fail or not if FTP connection is failed.

Once you have configured FTP connection, move to File Transfer tab where you need to specify Local path and Remote path either through File connection or an variable as shown in Figure 4.

  Figure 4

Remote path allows us to browse remote server files or directories and select as shown in Figure 5

Figure 5

For the purpose of example Create FTP connection with FTP server ftp.microsoft.com and choose file transfer operation as receive files and select Remote path as /bussys/readme.txt and localpath as Desktop. After configuration, Task editor will be shown as Figure 6. executing this package gets the file readme.txt from bussys folder onto desktop.

Figure 6

Read more ...