checking flat file against SQL table

Question

We have a user that wants to edit a flat file against sql data. I would like to set up a process where he can do this himself. I thought about trying to set up a local ssis package that would match the data against the table and identify the errors, but due to licensing issues, it sounds like that isn't an option. I thought about trying to set up a process to load the file into a temporary sql table and run queries, but looks like we don't have permissions to read data adhoc from a flatfile. There are too many records for the user to do a bunch of inserts into a temporary table, plus I am hoping to simplify this some for him. So basically, I'm looking for a 'no additional cost' solution that we can do that is in compliance with microsoft licensing and doesn't cause our database administrator any problems.

Answer 1

You could use a PowerShell script instead of SSIS for a simple ETL process run on the user's workstation. The PS script below bulk inserts data from a CSV on the user's workstation into a temp table and runs a validation query.

$csvFilePath = "C:\temp\CountryList.csv"
$connectionString = "Data Source=YourServer;Initial Catalog=tempdb;Integrated Security=SSPI"

try {

    #load csv into data table for import
    $countryList = Get-Content $csvFilePath | ConvertFrom-Csv
    $countryListTable = New-Object System.Data.DataTable
    [void]$countryListTable.Columns.Add("CountryName")
    foreach($country in $countryList) {
        $row = $countryListTable.NewRow()
        $countryListTable.Rows.Add($row)
        $row["CountryName"] = $country.CountryName
    }

    #create temp table with same schema as data table
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $command = New-Object System.Data.SqlClient.SqlCommand("CREATE TABLE #CountryList(CountryName nvarchar(100) COLLATE Latin1_General_100_CI_AS);", $connection)
    $connection.Open()
    $command.ExecuteNonQuery()

    #import csv data into temp table
    $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connection)
    $bcp.DestinationTableName = "#CountryList"
    $bcp.WriteToServer($countryListTable)

    #run validation query
    $command.CommandText = 
@"
SELECT 
     CountryList.CountryName
    ,CASE WHEN Countries.CountryName IS NULL THEN 'Invalid country name' ELSE 'Country name is valid' END AS Message
FROM #CountryList AS CountryList
LEFT JOIN WideWorldImporters.Application.Countries ON Countries.CountryName = CountryList.CountryName;
"@
    $dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
    $results = New-Object System.Data.DataTable
    $dataAdapter.Fill($results)

    # display results
    $results | Out-GridView

    $connection.Close()

}
catch {
    throw
}

Answer 2

Hi,

I thought about trying to set up a local ssis package that would match the data against the table and identify the errors, but due to licensing issues, it sounds like that isn't an option.

Do you have any license for SQL Server?

If so, then you can have the SSIS running at that machine and connect it remotely

we don't have permissions to read data adhoc from a flatfile.

Please clarify what permission you have. If you cannot read the file than how to plan to have this task done?

So basically, I'm looking for a 'no additional cost' solution that we can do that is in compliance with microsoft licensing and doesn't cause our database administrator any problems.

Why do even thinking about using SQL Server for the task?

You can use any local application or even simple powershell script to read the data from the table and read the data from the file and compare it in the app side :-)

By the way, if you have Azure subscription then you can also use Logic Apps for the task so it will be online service and not locally by each user in his machine

Answer 3

Hi EvanBrown-0001,

If you install SSIS on the same machine as the database engine, SSIS is included in the SQL Server license.
Please refer to SQL Server License Guide which might be helpful.
And for license issue, I suggest that you can consult professionals in this area calling 1-800-426-9400, 6:00 A.M. to 6:00 P.M. (Pacific Time). For international customers, please use the https://www.microsoft.com/en-us/licensing/worldwide to find contact information in your locations. For Volume Licensing Service Support, please use the https://learn.microsoft.com/en-us/licensing/contact-us to contact information for your country.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.