Excel Source

Not using SSIS:

Starting in version 6.1 of Profitbase Studio you can connect to Excel data directly; bypassing SSIS and Jet/ACE components.

You can avoid using SSIS by checking the "Skip SSIS" check box on the "Data Source" screen. See figure below:

PS6_Excel_Part3_SkipSSIS.PNG

Note! 

Unlike SSIS where data types are guessed based on data in the first 8 rows (see "Note!" at the bottom this page) data types are always imported as nvarchar(500). This may require you to use CONVERT or some string functions like REPLACE and/or SUBSTRING to properly convert numerical data and dates into valid formats.
Example 1: Decimal symbols can be changed from "," to "." using REPLACE(numericColumn, ',', '.')
Example 2: Date format can be hard coded to "yyyy-MM-dd HH:mm:ss" using
example 2.1: CONVERT(datetime, [Date], 104) (see https://msdn.microsoft.com/en-us/library/ms187928.aspx for CONVERT format codes) or 
example 2.2: SUBSTRING(DateColumn, 7, 4) + '-' + SUBSTRING(DateColumn, 4, 2) + '-' + LEFT(DateColumn, 2) + ' 00:00:00'. See example in the figure below:

PS6_Excel_Part2_Retail.PNG

 

Also note!

Source filters are applied to .Net data table using the following syntactic rules: https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx  

 

Using SSIS:

Excel worksheet data can be connected to via SSIS.

Xlsx files will use the ACE OLEDB provider. Xls files will use the Jet Provider. Providers can be installed from here:
Office 2010.xlsx:Microsoft Access Database Engine 2010 Redistributable  _x64 is recommended if 64 bit server.

Office 2016 xlsx: Microsoft Access Database Engine 2016 Redistributable  _x64 is recommended if 64 bit server.

For Excel sources the connection string can be manually changed by the user in order to override by using the IMEX=1 setting to better support columns with mixed data types.

If problems occur when importing data. A good way of trying to identify the problem is to reduce the amount of columns or rows in scope. This can be done by specifying a data range after the $ sign in the Table name field. See example in the figure below:

PS6_Excel_Part1_Retail.PNG

 

Note! 
Excel will by default check the first 8 rows to determine the data type of the columns. This will sometimes result in wrong data type. To resolve this limitation the TypeGuessRows registry key value can be changed. If this value is changed to zero (from default 8 to 0) the entire worksheet is scanned to determine which data type the columns should be. This should be done with caution though because scanning large worksheets can become a performance issue. 

 

Using PowerShell:

If neither SSIS or the non-SSIS option does the job. Using a PowerShell script to load the data is always an option. Below is an example that can be used to load Excel data:

$sql = @"
select * from [<<SOURCEFILENAME>>`$]
"@
try
{
    $filename = "<<FILEPATH>>\WorkbookName.xlsx"
    $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=${filename};Extended Properties=`"Excel 12.0 Xml;HDR=YES`"";
    $connection = New-Object System.data.OleDb.OleDbConnection $connectionString;
    $connection.Open();
    $command = New-Object System.data.OleDb.OleDbCommand $sql
    $command.Connection = $connection
    Add-Type -AssemblyName "Microsoft.SqlServer.Smo<<SMOVERSION>>" 
    $ns = 'Microsoft.SqlServer.Management.Smo'
    $svr = New-Object("$ns.Server") "<<DWSERVER>>"
    $db = $svr.Databases["<<DWDATABASE>>"]    
    $tbExistCheck = $db.Tables["<<TABLENAME>>_Temp_Temp"]
    if ($tbExistCheck)
    {
        $tbExistCheck.Drop()
    }
            
    $tbl = New-Object("$ns.Table")($db, "<<TABLENAME>>_Temp_Temp", "<<DWSCHEMA>>")
    $reader = $command.ExecuteReader()
    $schemaTable = $reader.GetSchemaTable();
    foreach ($row in $schemaTable.Rows)
    {
        $sqlDataType =""
        switch ($row["DataType"].ToString())
        {
            "System.AnsiString" {if ($row["ColumnSize"].ToString() -eq "0") {$sqlDataType = "VarCharMax";} else {$sqlDataType = "VarChar";}; break}    
            "System.AnsiStringFixedLength" {$sqlDataType = "Char"; break}
            "System.Binary" {$sqlDataType = "Binary"; break}
            "System.Boolean" {$sqlDataType = "Bit"; break}
            "System.Byte" {$sqlDataType = "TinyInt"; break}
            "System.Byte[]" {if ($row["ColumnSize"].ToString() -eq "8") {$sqlDataType = "Timestamp";} elseif ($row["ColumnSize"].ToString() -eq "2147483647") {$sqlDataType = "Binary";} else {$sqlDataType = "VarBinary";}; break}    
            "System.Currency" {$sqlDataType = "Money"; break}
            "System.Date" {$sqlDataType = "Date"; break}
            "System.DateTime" {$sqlDataType = "DateTime"; break}
            "System.DateTime2" {$sqlDataType = "DateTime2"; break}
            "System.DateTimeOffset" {$sqlDataType = "DateTimeOffset"; break}
            "System.Decimal" {$sqlDataType = "Decimal"; break}
            "System.Double" {$sqlDataType = "Float"; break}
            "System.Guid" {$sqlDataType = "UniqueIdentifier"; break}
            "System.Int16" {$sqlDataType = "SmallInt"; break}
            "System.Int32" {$sqlDataType = "Int"; break}
            "System.Int64" {$sqlDataType = "BigInt"; break}
            "System.Object" {$sqlDataType = "Variant"; break}
            "System.SByte" {$sqlDataType = "SmallInt"; break}
            "System.Single" {$sqlDataType = "Real"; break}
            "System.String" {if ($row["ColumnSize"].ToString() -eq "0") {$sqlDataType = "NVarCharMax";} elseif (-not $row["DataTypeName"]) {$sqlDataType = "NVarChar";} elseif ($row["DataTypeName"].ToString() -eq "varchar") {$sqlDataType = "VarChar";} else {$sqlDataType = "NVarChar";}; break}    
            "System.StringFixedLength" {$sqlDataType = "NChar"; break}
            "System.Time" {$sqlDataType = "DateTime"; break}
            "System.UInt16" {$sqlDataType = "Int"; break}
            "System.UInt32" {$sqlDataType = "BigInt"; break}
            "System.UInt64" {$sqlDataType = "BigInt"; break}
            "System.VarNumeric" {$sqlDataType = "Numeric"; break}
            "System.Xml" {$sqlDataType = "Xml"; break}   
        }
        if ($row["IsLong"].ToString() -eq "True") {$sqlDataType = "NVarCharMax";} 
     
        $colName = $row["ColumnName"].ToString()
        $dtype = [Microsoft.SqlServer.Management.Smo.SqlDataType] $sqlDataType

        if ($sqlDataType -eq "VarChar" -or $sqlDataType -eq "NVarChar" -or $sqlDataType -eq "Char" -or $sqlDataType -eq "NChar")
        {
            $length = $row["ColumnSize"].ToString()
            $dtype = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType, $length.ToInt32($Null))
        }
        elseif ($sqlDataType -eq "Decimal" -or $sqlDataType -eq "Numeric")
        {
            $precision = $row["NumericPrecision"].ToString()
            $scale = $row["NumericScale"].ToString()
            $dtype = New-Object Microsoft.SqlServer.Management.Smo.DataType($sqlDataType, $precision.ToInt32($Null), $scale.ToInt32($Null))
        }

        $colconame = New-Object("Microsoft.SqlServer.Management.Smo.Column")($tbl, $colName, $dtype)
        $tbl.Columns.Add($colconame)
    }
    $reader.Close()
    $tbl.Create()
    $connectionstring = "Data Source=<<DWSERVER>>;<<DWAUTH>>;Initial Catalog=<<DWDATABASE>>;"
    $bulkcopy = New-Object("Data.SqlClient.SqlBulkCopy") $connectionstring
    $bulkcopy.DestinationTableName = "<<DWSCHEMA>>.<<TABLENAME>>_Temp_Temp"
    $bulkcopy.BulkcopyTimeout = <<TIMEOUT>>
    $bulkcopy.BatchSize = 4000 # Batches of 4k rows is usually fast and keeps memory usage low
    $bulkcopy.EnableStreaming = 1
    $command.CommandTimeout = <<TIMEOUT>>;
    $readrow = $command.ExecuteReader()
    $bulkcopy.WriteToServer($readrow)
}
catch
{
    write-error $_.Exception.Message
}
finally
{
    $connection.Close()
}
[System.GC]::Collect()