I know this is an old post and it has nothing to do with SSIS nor SSDT but the OP was seemingly inquiring about other options that maybe available. So I wanted to add this for anyone who is looking for a easy way to import extract files without having to use either of those tools. I have created literally hundreds of processes where I'm required to import data from an extract file (CSV, Access, Excel, FoxPro, etc) the following is a Powershell snippet that will load up all sheets in an Excel Spreadsheet and then simply display the contents in a Data Grid but you should be able to easily add in your logic to import the data into a table for example. Constructive criticism is always welcome!
Clear-Host;
## You May Need to Download and Install the Microsoft Access Database Engine 2010 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=13255
[String]$ExcelPath = "C:\Temp\TestSheet.xlsx";
[String]$TargetServer = "(local)";
[String]$TargetDatabase = "TestDB";
[String]$SourceConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES';" -f $ExcelPath;
[String]$TargetConnectionString = "Data Source={0};Initial Catalog={1};Trusted_Connection=True;" -f $TargetServer, $TargetDatabase;
$SourceFactory = [System.Data.Common.DbProviderFactories]::GetFactory("System.Data.OleDb");
$TargetFactory = [System.Data.Common.DbProviderFactories]::GetFactory("System.Data.SqlClient");
$SourceConnection = $SourceFactory.CreateConnection();
$SourceConnection.ConnectionString = $SourceConnectionString;
$SourceConnection.Open();
$SourceCommand = New-Object $SourceFactory.CreateCommand();
$SourceCommand.Connection = $SourceConnection;
$SourceCommand.CommandTimeout = [Int32]::MaxValue;
$TargetConnection = $TargetFactory.CreateConnection();
$TargetConnection.ConnectionString = $TargetConnectionString;
$TargetConnection.Open();
$TargetCommand = New-Object $TargetFactory.CreateCommand();
$TargetCommand.Connection = $TargetConnection;
$TargetCommand.CommandTimeout = [Int32]::MaxValue;
foreach($table in $SourceConnection.GetSchema("Tables").Rows){
try{
## Source
[String]$TabName = $table["TABLE_NAME"];
[String]$sqlString = "SELECT * FROM [{0}];" -f $TabName;
$SourceCommand.CommandText = $sqlString;
[System.Data.Common.DbDataReader]$SourceDataReader = $SourceCommand.ExecuteReader();
$dtData = New-Object System.Data.DataTable;
$dtData.Load($SourceDataReader);
## Target -- Bulk Insert data
if($dtData.Rows.Count -gt 0){
$TabName = "[{0}]" -f $TabName;
$sqlBulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($TargetConnection);
$sqlBulkCopy.DestinationTableName = $TabName;
foreach ($Column in $dtData.Columns){
$sqlBulkCopy.ColumnMappings.Add($column, $column)
};
$sqlBulkCopy.WriteToServer($dtData);
}
}catch{
$table["TABLE_NAME"];
$_.Exception.Message;
$_.Exception.ItemName;
};
};
#Housekeeping
$sqlBulkCopy.Close();
$sqlBulkCopy.Dispose();
$SourceCommand.Dispose();
$SourceDataReader.Dispose();
$SourceConnection.Close();
$SourceConnection.Dispose();
$TargetCommand.Dispose();
$TargetDataReader.Dispose();
$TargetConnection.Close();
$TargetConnection.Dispose();
$TargetConnection.Close();
$TargetConnection.Dispose();
[System.GC]::Collect();
