SSIS

  • Transforming Excel data can be a complex task this article describes how the Advanced ETL Processor works with Excel data.

    Reading EXCEL data

    Data can be extracted from the entire Sheet or just from the data range.
    Advanced ETL Processor assumes that "Sheet name" and "Range name" a delimited by a dot.

    Users can still call Sheets as a "Great.Victory"

    The logic as follows:

    Advanced ETL Processor will check if Great.Victory sheet exits than it will read it
    If it does not exist it will check if Great sheet exits
    If it does exists it will check if Victory range exists if it does It will read data from just Victory range
    If it does not it will read the data from Sheet "Great"

    Please make sure that the range names are not duplicated within the file

    Writing EXCEL data

    Advanced ETL Processor gives the user various options to generate Excel files.

    Transforming Excel data

  • Solving problems with loading data from Excel files into databases

    Common problem:trying to load the data from Excel file half of the data is coming as nulls, or columns with more than 255 characters are truncated

    The logic behind Excel mixed data types

    As partially explained here

    http://support.microsoft.com/kb/257819

    ODBC/MS Jet scans first TypeGuessRowsto determine field type

    Here how Excel ODBC/MS Jet works

    (TypeGuessRows=8 IMEX=1)

    In your eight (8) scanned rows, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
    In your eight (8) scanned rows, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) null values and five (5) text values.
    In your eight (8) scanned rows, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) numbers and four (4) null values.
    In your eight (8) scanned rows all of them less than 255 characters the provider will truncate all data to 255 characters
    In your eight (8) scanned rows, if the column contains five (5) values with more length than 255 the provider will return more than 255 characters

    NOTE:

    Setting IMEX=1 tells the driver to use the Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight-row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

         Nobody wants to load half of the data, everybody wants to load data as it is

    The only way to make import from Excel work is

  • According to Wikipedia "FTP was not designed to be a secure protocol, and has many security weaknesses" and yet it the only protocol supported by SSIS. This fact alone makes this SSIS component practically useless

    FeatureSSISAdvanced ETL Processor Ent/Visual Importer ETL Ent
      FTP - File Transfer Protocol Supported Supported
      FTPS - FTP over implicit TLS/SSL Not Supported  Supported
      FTPES - FTP over Explicit TLS/SSL Require TLS Not Supported Supported
      FTPES - FTP over Explicit TLS/SSL Not Supported Supported
      SFTP - SSH File Transfer Protocol Not Supported Supported
      FTP - Port Ranges Not Supported Supported
      Downloads Supported for FTP protocol only Supported
      Uploads Supported for FTP protocol only Supported
      Create Remote Directory Supported for FTP protocol only Supported
      Delete Remote Directory Supported for FTP protocol only Supported
      Delete Files Supported for FTP protocol only Supported
      Rename File Not Supported Supported
      Directory List Not Supported Supported
      Move Files Not Supported Supported

     

    SSIS FTP

    AETL FTP

    AETL FTP4

    AETL FTP2

    AETL FTP3

     

    Download Free Trial Learn More About Our ETL Software
  • The new version of Advanced ETL Processor is available for download

    This version addresses well-known problems related to working with Excel files

    • Works directly with all versions of Excel
    • No ODBC, OleDB or MS Jet Required
    • Works correctly with mixed data types
    • Works correctly with cells with more than 255 characters
    • No need for IMEX=1, HDR=Yes or Registry hacks (TypeGuessRows)
    • Works with ranges
     
    • Loads data correctly all the time + no need to edit Excel file
    • Can create Excel files in Excel 3.0-2016 format
    • Can insert data starting from specific cell
    • Can clear area before adding data into Excel
    • Can add headers

    Loading Data from Excel File

    List of Microsoft Excel ODBC driver bugs you do not need to deal with anymore

    1. PRB: Excel Values Returned as NULL Using DAO OpenRecordset
    2. Data truncated to 255 characters with Excel ODBC driver
    3. Excel ODBC Driver May Determine Wrong Data Type
    4. BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting
    5. How To Use ADO with Excel Data from Visual Basic or VBA
    The sad story is: what ever you use: Excel ODBC, MS Jet, Ole DB, .NET sooner or later you will hit the brick wall.
    You can not load the data correctly without modifying excel file. We receive thousands of excel files every day.
    I mean do you seriously expect us to modify them manually every day? What kind of automation is that?
    We tried DTS it did not work tried SSIS it did work either, so we wrote vb macros to extract the data.
    We do not do it any more since we have discovered Advanced ETL processor It saved us thousands of hours.
    John Spoon.

    Also Data grids modifications:

    • Grouping panel was added
    • Advanced filter dialogue was introduced
    • Record number in the grid
    Try it yourself now

    VIDEO TUTORIAL

    Download Free Trial Learn More About Our ETL Software
  • Advanced ETL Processor is true real alternative to SSIS. It's highly advanced capabilities make SSIS look like a toy.

    VAST NUMBER OF CONNECTORS

    Text, XML ,Excel, QVX, QVD, MS Access Database, DBF, Clarion, ODBC, JDBC, OleDB, MongoDB, MS SQL Server, MS SQL Server CE, Oracle, MySQL, PostgreSQL, Interbase/Firebird, SQLite, File System, POP3, IMAP4, HL7, Google SpreadSheet, RSS Feed, SalesForce, BrightPearl, BDE, HTTP, FTP, Microsoft Message Queue, Windows Event Log, Active Directory and JSON

    Download Advanced ETL Processor Enterprise now!


    Alternative to SSIS

    GREAT PERFORMANCE - THOUSANDS OF RECORDS PER SECOND

    etl performance

    Performance depends on data and complexity of the transformation

    TRANSFORM THE DATA - ANY WAY YOU WANT

    Advanced ETL Processor has more than 500 transformation and validation functions. if something is missing we will add for you. Data can be sorted grouped or filtered, the possibilities are limitless. 


    Transform Excel files anyway you want

    WORKFLOW EXAMPLE

    Automatic Invoice Processing

    Workflow operations

    all package automation actions

    WE DO EVERYTHING TO MAKE LIFE OF DEVELOPERS EASIER

    Here is SSIS Ftp task editor. There is no much to change or see here. Plus FTP is not secure protocol so it makes it useless in the corporate environment.

    ssis ftp task

    Here is Advanced ETL Processor FTP Action Properties dialogue. The users can see the files and perform necessary FTP actions.

    ftp package action

    UNIVERSAL DataReader AND WRITER

    Unlike SSIS, Advanced ETL Processor avoids having different connectors, many of which are very subtle variations on similar components. Instead, Advanced ETL Processor uses only one universal Data writer and Data reader component but each one is highly configurable and includes all of the parameters needed to get your data in and out of almost any data source. This approach allows the end user to design mapping once and use it with any database or file.

    MONITOR SERVICES

    The monitor is windows service waiting for an event to happen. When it does it executes the predefined action. we offer the following

    Monitor TypeDescription
    Directory Monitor Tracks folder changes, such as file creation, removal, modification or rename.
    FTP Monitor Tracks new files arrivals
    IMAP4 Monitor Tracks new email arrivals
    POP3 Monitor Tracks new email arrivals
    MSMQ Monitor Checks Message Queue (MSMQ) for new messages.
    HTTP Monitor a web server which executes an action when predefined URL is open.

     Interested?

    Contact us now!

    VIDEO TUTORIAL

  • Solving SSIS problems with loading data from Excel

    We have the following situation:

    Imagine that you are working for a large finance organization and would like to load the year-end balances into SQL Server database. The excel file looks like this.

    Excel File

    So we create a simple SSIS package using Excel connector and Ole DB destination, before loading the data we hit preview button.

    SSIS Preview Result

    For some reason last 3 lines have null values, This is because our connection string has not got IMEX=1

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES";

    We correct it

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DEMO\Excel\mixed_data_types.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

    SSIS Preview Result

    That looks much better so we load the data. Later the same day, the finance manager says that the balance is wrong you look at the data and there are some null values again.

    Why DO we have nullS in the database?

  • Advanced ETL processor vs SSIS.

    Development Efficiency against wasting time

    One of our Insurance customers has contacted us recently and asked for immediate assistance. They were trying to implement XML files processing for a number of weeks using SSIS.
    Our consultant was able to deliver a working solution within one day using Advanced ETL Processor.

    Here is what he did:

    Background

    The XML files show up every day.  The client places them on FTP server (which means that the file is locally found on our network via a UNC path. )
    Each nested XML tag should be a new “related” table.
    Main XML tag is "Contracts", Other relevant tags are Billing, Coverage, Person, Risk and Underwriting Question.
    There are a couple of data issues as bad dates from the customer. They should be ignored and inserted into the table as a Null.
    The process should be scheduled to check a UNC folder on the network at least 3 times per day.
    If one or more XML files exist, they need to be loaded into the server.
    Once loaded an Email should be sent to me indicating success, failure or nothing to do.  
    There are some duplicated data due to the relationships of the data.
    For example, each contract has Agency information.  So any contract MAY use a previously inserted Agency In this case Update would be appropriate.
    Updating data, if the same contract is loaded the second time all relevant data must be deleted first

    Solution

    For every nested XML tag, a relevant data transformation was created.
    One of the main problems was that XML format was not stable, some of the files have missing tags or additional tags.
    That was addressed by using custom XSLT transformations

    SSIS Package Alternative

    Then the package was created to combine all those transformations together

    SSIS Package Alternative

    We have been fighting this one with SSIS for about 2 weeks and got really behind schedule. Then I remembered we own Advanced ETL Processor so I thought we should give it a shot. We are just so far behind that we need this ASAP and I did not want to mess it up. The result was far beyond our expectations. They were able to do more in one day than entire team in two weeks.
     If you are struggling with SSIS or any other tools and have very tight deadline contact us and we will do our best to help you today.

    Note

    In order to speed up the process please provide as much information as you can.

    • Several data examples
    • Table creation scripts
    • Remote access details
    • Have someone immediately available for questions
    Tutorials Download 32 Bit Download 64 Bit Buy It

We have 246 guests and one member online