SSIS Excel Source
PreviousNext

SSIS Excel Source can be used to read excel data using Table mode or Query mode (It supports 64Bit and 32Bit mode without installing any additional driver).

Download SSIS PowerPack

Content

Video Tutorial

Step-By-Step

SSIS Excel File Source Connector (Advanced Excel Source) can be used to read the Excel file without installing any Microsoft Office Driver. You can extract using Table mode or query mode (SQL Command). Support for flexible metadata detection mode so you can get accurate datatypes
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. Here, In Visual Studio, drag and drop the ZS Excel Source in the design surface.
    SSIS Excel Source - Drag and Drop
  6. We need Excel File Connection. So lets create Excel File Connection.
  7. Right click on Connection Managers Panel to Create New ZS-Excel Connection and Context Menu will appear, Select New Connection from the Context Menu.
    SSIS Create New Connection
  8. Select ZS-EXCEL Connection Manager from the Connection Managers list and Click on Add Button
    SSIS Excel Source - Connection
  9. Now in opened excel connection manager window set Source Path with file name and suitable extension. Also Pattern is only applicable for Excel Source. If you use excel destination.. it must use single file.
    Note: If you want to operation with multiple files then use wildcard pattern as below (when you use wildcard pattern in source path then system will treat source path as folder regardless you end with slash).
    
    Examples:
    c:\SSIS\Excel\file123.xlsx (single file).
    c:\SSIS\Excel\file*.xlsx (all files starting with name file).
    c:\SSIS\Excel\subfolder\*.xlsx (all files with .xlsx Extension and located under folder subfolder).
    

    SSIS Excel Source - Connection Configure
  10. Click OK to save Connection Manager setting UI.

How to SSIS Excel File Source Read from Excel File using Table Mode.

  1. Double click on ZS Excel Source to configure it.
  2. Select Excel Connection we have created before, Set Access Mode to Table and Select Table or you can read from any sheet by number rather than hardcoded table name (e.g. $3$ , $4$ ... ) of your Excel Sheet.
    SSIS Excel File Source - Table Mode
  3. Click on Preview button to see Data Preview.
  4. Click OK to save settings.
  5. Now, Just Drag and Drop Our Free ZS Trash Destination from SSIS Toolbox.
    SSIS Trash Destination - Drag and Drop
  6. Now single click on the Excel Source, once you see blue arrow from source ... connect it to Trash Destination.
  7. Lets Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Drag and Drop
  8. That’s it we are ready to load excel file data.
    SSIS Excel File Source - Run or Execute

How to SSIS Excel File Source Read from Excel File using SQL Query Mode.

  1. Here, we are getting data using SQL Query with Dynamic expression value.
  2. Lets, create a Variable with correct DataType and Value(You can write SQL Query in value too).
    SSIS Excel Source - Create Variable
  3. We can also read Excel Workbook using SQL Query mode and filter the data. Select Access Mode Query. Write SQL Query and Use Variable we have created for condition.
    SELECT TOP 5 * FROM [Sheet1] 
    Where [OrderID] < {{User::varOrderID}}
    ORDER BY OrderID DESC
    
    SSIS Excel File Source - SQL Query Mode
  4. Click on Preview button to see Data Preview.
  5. Click OK to save settings.
  6. You can do various other things also in the query mode, find our various example here to read Excel Workbook using SQL Query mode.
    SSIS Excel File Source - Examples
  7. Now, Just Drag and Drop Our Free ZS Trash Destination from SSIS Toolbox.
    SSIS Trash Destination - Drag and Drop
  8. Now single click on the Excel Source, once you see blue arrow from source ... connect it to Trash Destination.
  9. Lets Double click on ZS Trash Destination to Configure it.
    SSIS Trash Destination - Drag and Drop
  10. That’s it we are ready to load excel file data.
    SSIS Excel File Source - Run or Execute

Properties

Property Name Description
LoggingMode LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.
TableName Sheet name or named range indicator from which you like to read data. You can use special table name indicators like [$first_sheet$] or [$1$], [$last_sheet$] or [$SomeNumber$]. You can also use Regular expression to get data from multiple sheets with matching name. Syntax:[regex-pattern]--regex (e.g. MySheet_\d+--regex), for example to get all rows from Sheet names Sales-01,Sales-02..Sales-12 you can set Table name as Sheet-(\\d+)--regex . For named range you can use NamedRange:NAME_FOR_RANGE
AccessMode AccessMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Table [0] Table
Query [1] Query
Query Query to read data. This option only used when AccessMode=Query
MaxRows Maximum number of rows to fetch from source. 0=Unlimited
FormatAllColumns Output all columns as per excel formatting
SkipRows Total number of rows which needs to be skipped (After header)
SearchStringForTabSelect Data to search for Sheet selection (enter static string or regular expression with suffix --regex to search for). To use this property you need to set table name with --regex suffix (e.g. Sales(\w+)--regex) and out of all those Sheets it will select only those sheets for data read which has search string found. To search for string in all sheets you can use (.*)--regex as Table name. You can enter static string to search for (e.g. "SALES ORDERS-2023") or use Regular expression in the search string e.g. "SALES ORDERS-(\d+)--regex". This property is useful when you dont know Sheet name but you have idea what data you want to look for somewhere in all tabs.
Headerless Data has no header row to indicate column names
FormattedColumns List of column(s) which needs to be formatted according to excel format. Multiple columns must be separated by vertical bar (e.g. BirthDate|Amount|Age)
RangeStartCell Range start address for data read/write. Address must be excel cell address (e.g. A1)
RangeEndCell Range end address for data read/write. Address must be excel cell address (e.g. A1)
MetaDataScanMode Metadata scan mode controls how data type and length is determined. By default few records scanned to determine datatype/length. Changing ScanMode affects length/datatype accuracy.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Auto [0] Auto
Strict [1] Strict - Exact length
Guess2x [2] Guess2x - 2 times bigger
Guess3x [3] Guess3x - 3 times bigger
Guess4x [4] Guess4x - 4 times bigger
TreatAsUnicodeString [5] Set all columns as string
Guess10x [6] Guess10x - 10 times bigger
TreatStringAsMaxLength [7] Set string columns with MAX Length - i.e. DT_WSTR(4000)
TreatStringAsBlob [8] Set string columns as BLOB - i.e. DT_NTEXT
MetaDataCustomLength Length for all string column. This option is only valid for MetaDataScanMode=Custom
MetaDataTreatStringAsAscii When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode)

Setting UI

SSIS Excel File Source - Setting UI
SSIS Excel File Source - Setting UI
SSIS Excel File Source - Setting UI
SSIS Excel File Source - Setting UI

See Also

Articles / Tutorials

Click here to see all articles for [SSIS Excel Source] category
SSIS PowerPack v2.9.5 released (Maintenance Release)

SSIS PowerPack v2.9.5 released (Maintenance Release)

What’s New In this release we focused on fixing some bugs with Dynamics CRM Source , Dynamics CRM Destination and Reporting Services Task. Version 2.9.6.10621 [Jun 24, 2019] New Features/Improvements NEW: Excel Source – Add option to read from any sheet by number rather than hard coded table name (e.g. $4$ , $5$ … ) Bug fixes […]


Load data into PostgreSQL from Excel file using SSIS

Load data into PostgreSQL from Excel file using SSIS

Introduction In this post, we will learn How to Load data into PostgreSQL from Excel file using SSIS PostgreSQL Destination.  We will use SSIS PowerPack to connect PostgreSQL. This article also covers how to read Excel file in SSIS. SSIS PostgreSQL Destination can be used to perform bulk insert in PostgreSQL Table from any data source using SSIS. […]


How to read excel file in SSIS – Load into SQL Server

How to read excel file in SSIS – Load into SQL Server

Introduction In this post, we will learn How to read excel file in SSIS – Load into SQL Server.  We will use SSIS PowerPack to connect Excel file. This article also covers how to read Excel file in SSIS. SSIS Excel File Source Connector (Advanced Excel Source) can be used to read Excel files without installing any […]


SSIS PowerPack v2.9.2 released (Maintenance Release)

SSIS PowerPack v2.9.2 released (Maintenance Release)

What’s New In this release we focused on mainly focused on fixing high priority bugs related to Upsert Destination. We also added High DPI Monitor Support with Auto Scale UI option (4K Resolution now supported) Full Notes: https://zappysys.com/onlinehelp/ssis-powerpack/scr/release-notes.htm Version 2.9.2.10321 [Mar 21, 2019] New Features/Improvements NEW: Excel Source – Add Option to read first sheet and […]



Copyrights reserved. ZappySys LLC.