In the article, we will take a look at how we can automate the process of creating a Table in Microsoft SQL Server and importing data from an Excel file. We will be using the Data Transformation Alert Type for this example. It is assumed that you have already updated the Customer ID and Pin in the Options window. If you don't have this information, please contact us to get a trial account.
Please note that some of the settings in your Connection Strings could change depending on your system. We strongly recommend that you execute the Alert on a Staging/Development Server before you deploy it to your Production Server.
We have used Microsoft SQL Server Express 2017 here, but the SQL statement should pretty much work on earlier versions of SQL Server and SQL Server Express (at least from 2008).
We have created a Database called AutomationDB and we intend creating a Table called ImportTest in this database. The fields should be according to the Excel file we have in XLS format (you will have to make a few changes in your Connection String to do the same thing from XLSX files).
Also keep in mind that the Table is created only the first time the Alert runs and subsequent runs will only return an error. You should have a mechanism in your application to remove the Table before you run this Alert again. The purpose of this example is only to show how a Table can be created by using a BitFaster Alert in SQL Server.
Let's start by creating a Connection String for the SQL Server as below. Make sure you click on the Test button to make sure BitFaster is able to connect to the database.
New Connection String - SQL Server
The Excel data that we wish to import is shown below. The first row has the Field names. So the Table we create in SQL Database should retain the same Field names. Make sure the Excel file is closed.
Excel Data with Field Names
Now let's start by creating a new Alert. You can choose File > New > Alert from the menu. The Alert Name could be a brief description of what you wish to accomplish (up to 100 characters). The Comments field is optional.
Specify Alert Name
You can now click on the Data Source tab and choose the Connection String you created before and type in the SQL statement. The statement we have used is shown below, but this could vary a bit depending on your local system settings and drivers available.
Select * into ImportTest FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\Users\user\Desktop\Campaigns\ImportToSQLTable.xls;HDR=YES;IMEX=1', 'SELECT * FROM [Sheet1$]')
Connection String and SQL Statement
Now let's test this SQL statement by using the Run Query option. You should get the message "No results returned" which confirms that the SQL statement was executed successfully.
Let's check the results from the SQL Server Management Studio window. You can see that the table ImportTest is created and data from the Excel file is imported as intended.
SQL Server Management Studio View