Archive

Posts Tagged ‘SSIS’

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

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 ….