This data sheet provides high level examples of loading source data into SQL Server as a staging environment where you can analyse, clean, transform and generally prepare for a load to Salesforce with SQL-Sales.
This first example, will take a source file “Legacy Accounts.csv” and load into SQL table SourceAccountDataDemo, as referenced in data sheet “Getting started with SQL-Sales and SQL Server Express“.
Import Flat File
Right click the Database you wish to load to, choose Import Flat File
Click Next
Browse to your source file and enter the new SQL table you wish to load to
Preview the data, for the purposes of this demonstration we are going to accept all defaults, click Next
We will tick Allow Nulls and note that you can specify an appropriate data type for the source columns, for this demonstration we’ll accept the defaults
Click Finish
The load has completed, click Close
Here is the source data and the newly created SQL table
This second example, will take a source file “Legacy Contacts.csv” and load into SQL table SourceContactDataDemo, as referenced in data sheet “Getting started with SQL-Sales and SQL Server Express“.
Import Data
Right click the Database you wish to load to and this time, rather than Import Flat File, we’ll explore using Import Data
Click Next
For Data source choose Flat file Source
When browsing for your File, by default it will be looking for a text file with a .txt file extension, for our simple csv example, we’ll select .csv
All other options will be left as the default settings. The Import Data tool is very flexible and powerful but this data sheet is only showing a simple working end to end example.
Clicking Next and you’ll see the source Columns
Clicking on Advanced allows a customisation per column where necessary
Preview, click Next
Now choose a Destination, pick Microsoft OLE DB Provider for SQL Server
Your SQL Server instance will appear along with your specified target Database. Here the Authentication is the default Windows Authentication. click Next
By Default, a target table name will be generated, based on the source text file, in this case [Legacy Contacts]
This can be customised as required – in this example changed to SourceContactDataDemo
We’re all done – now click Next to run immediately
Final confirmation, click Finish
All done!
Final check – selecting from the newly created SQL table we see the data has loaded OK and in the Object Explorer on the left we see the columns.