SQL Server and MS BI With a Geek Touch

Bulk insert task is used to copy large amount of data into SQL Server tables from text files. For example, imagine a data analyst in your organization provides a feed from a mainframe system to you in the form of a text file and you need to import this into a SQL server table. The easiest way to accomplish this is in SSIS package is through the bulk insert task.

Configuring Bulk Insert Task

Drag the bulk insert task from the toolbox into the control flow window.

Read more…

What is For Loop Container?

For loop defines a repeating control flow. It has similar behavior as the “FOR” loop available in programming languages.  The For Loop container uses 3 expressions as definition

1. Initialization expression:  it assigns value to the loop counters. This expression is optional.
2. Evaluation expression : It contains the expression to test whether the loop should continue executing  or exit.
3. An optional iteration expression that increments or decrements the loop counter.

The expressions used must be valid SSIS expression. A variable is generally used in the expression.

Using and Configuring For Loop Container

(Task in hand: print counter variable value for each iteration times.)

Drag a for loop container into the SSIS Control flow window from the Toolbox. Double click on the for loop container to view the “For Loop Editor”. As mentioned earlier it contains 3 expressions. Now create a variable by name “TestVariable” of type “int32”.

Use this variable as counter in the container using expressions as shown below.

Read more…

Sequence containers is used to group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow. There are several advantages in using sequence container

1. Provides facility to set property of multiple tasks by setting the property of sequence container.
2. Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.

1. Scope for variables that a group of related tasks and containers use can be controlled easily.
2. Managing and visualization of group of tasks becomes easier using sequence container.
3. We can also expand and collapse the container, hence making the package look neat.

Using Sequence Container

Create a new SSIS package ( refer video to create new SSIS package). Drag the sequence container from the toolbox on the left hand side into the package.

Read more…

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14^th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14^th Jan. To do this,

1. go to properties window of the excel connection manager
2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

“C:\\Taxonomy_”+ (MONTH( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) MONTH( GETUTCDATE()  ) DT_WSTR,2)MONTH( GETUTCDATE()  ))

+(DAY( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) DAY( GETUTCDATE()  ) DT_WSTR,2)DAY( GETUTCDATE()  ))

+(DT_WSTR,4)YEAR( GETUTCDATE()  ) +”.xls”

Also you need to use the settings as below for connection manager ( for .xls files)

Read more…

The video shows the dynamic functionality of SSIS packages. I have used the ETL to read through multiple excel files dynamicaaly using a single connection manager. a For each loop container is used to loop through each of these files and the data loaded into SQL database by dynamically changing the connection string for each excel file. You can also download high quality video by clicking here.

Part 1:

Part 2:

Please leave a comment with your suggestions and feedbacks.

This video takes you through the creation of SQL Server Integration Services (SSIS) project creation. The video will also demonstrate the usages execute sql tasks and will take you through various components and windows in SSIS UI and the purpose of each. you can also download high quality video from by clicking here

Let us know if you would like to watch some specific videos.

It is very simple to schedule the SSIS packages using windows Task Scheduler. Just follow the steps below.

Step 1: create a batch file (.bat or .cmd) file with the command to execute the SSIS packages using “dtexec” it may be as simple as the command below.

“C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /FILE “C:\FY11PackagesAndReports \DataPullv2.dtsx” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING EWCDI >C:\ FY11PackagesAndReports \logsIP.txt

Step 2: Save this batch file (.bat) in a folder.
Step 3: Select Task scheduler from the programs ( start Menu)

Read more…

In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will often come across errors as shown below.

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager“: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. Read more…

Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B. This was due to formating in the excel, which was also used as a report by many stake holders.

Although at first it might seem like there are no options in excel source to skip certain rows from the beginning of the sheet, it is not true. Fortunately, excel source provides the feature to select only required records from any sheet.  the OpenRowset property on the Excel Datasource component  allows you to specify the range to be considered by the datasource.

Read more…

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Now you might think, What is so great about GUID? Although it occupies lot of space there are several advantages. First, a GUID will be a completely unique value and no other GUID in the world will share the same string. This means that you can use GUIDs as PKs on your tables if you will be moving data between databases. This technique prevents duplicate PKs when you actually copy data. GUIDs are widely used in Microsoft products to identify interfaces, replica sets, records, and other objects.

In Sql Server, you can create a new GUID by using the function NEWID(). This function is a non-deterministic function. It generates a new value every time you execute function .

Select NEWID()

 the datatype is named in SQL as “uniqueidentifier”