Archive

Archive for the ‘SSIS’ Category

SSIS : Using and Configuring For Loop Container

July 5, 2014 Leave a comment

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…

 

 

SSIS : Sequence Container

July 5, 2011 Leave a comment

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…

 

Creating Excel Files (.xls) dynamically from SSIS

April 25, 2011 1 comment

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 14th 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 14th 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.

Continue reading..

.

SSIS Error : Resolve transformation errors in 64-bit version of SSIS in debug mode

October 8, 2010 Leave a comment

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.

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

In Debug mode it is really easy to overcome this by doing a simple change in settings.

Continue Reading …

SSIS – Skipping rows and columns in Excel

October 7, 2010 Leave a comment

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.

  By specifying a range, I was able to ignore all the titles and data spread all over the sheet . The configuration is simple and is as shown in the screenshot below.

Continue Reading ….

 

So what do we call SQL Server 2011 ?

June 18, 2010 Leave a comment

We had been sticking onto the mountains names from a very long time for SQL server. The proposed release of SQL server ( 2011) is not gonna be different. I always had problems in memorizing these code names . however, it is still fun to hear these names and makes you feel like you calling a might warrior’s name from one of those arcade games.

Here is the list of SQL server Version and the code names.

Version Release Name Codename Year
1 SQL Server 1.0 1989
(OS/2) (16bit)
1.1 SQL Server 1.1 1991
(OS/2) (16bit)
4.21 SQL Server 4.21 SQLNT 1993
(WinNT)
6 SQL Server 6.0 SQL95 1995
6.5 SQL Server 6.5 Hydra 1996
7 SQL Server 7.0 Sphinx 1998
SQL Server 7.0 Plato 1999
OLAP Tools
8 SQL Server 2000 Shiloh 2000
8 SQL Server 2000 Liberty 2003
64-bit Edition
9 SQL Server 2005 Yukon 2005
10 SQL Server 2008 Katmai 2008
10.5 SQL Server 2008 R2 Kilimanjaro (aka KJ) 2010
  SQL server 2011 Denali 2011


The post is also available at our website: http://geekepisodes.com/2010/06/15/so-what-do-we-call-sql-server-2011/

Categories: SQL Queries, SQL Server, SSIS

Excel and SSIS 64-bit Connections

June 18, 2010 Leave a comment



I stumbled upon a new error yesterday. I developed a simple and straight forward SSIS package in my machine. It was meant to copy data from an Excel 2007 sheet to a table. It worked fine in my machine, but as soon as I moved it to  the server the package started failing. I had never come across this particular error before.

Source: ImportClientXLS Connection manager “Excel Connection Manager”
Description: 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.

then I realised I had a 32-bit machine and the server was 64-bit. And the dtexec used in 32 b was different from 64 b machines . The resolution to this error is simple. I just changed the command string.

My initial command string was

dtexec /f “D:\MigrationPackages\B2S\EPGSE.dtsx”

The  above string point to 64 b programfiles folder in 64 b machines and hence I changed it to point to 32 b programfiles folder.

“C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /f “D:\MigrationPackages\B2S\EPGSE.dtsx” /X86

and it worked perfectly fine and my day saved.;)

The post is also available at our website: http://geekepisodes.com/2010/06/12/excel-and-ssis-64-bit-connections/