SQL Server Integration Services (SSIS) is a powerful tool that allows for complex data import operations. In a previous article, we discussed how to import a CSV file into a database table when the file structure corresponds to the table structure. However, what if the CSV file structure does not match the database schema?
Let’s consider a scenario where we want to import a list of products with their prices into a Salesforce cloud database. Salesforce has a predefined database structure with three tables: Product2 (containing information on products), PriceBook2, and PriceBookEntry (the table that actually contains prices). PriceBookEntry has foreign keys to PriceBook2 and Product2.
To accomplish this task, we can use dotConnect for Salesforce from Devart, an ADO.NET provider for accessing data in Salesforce. This tool eliminates the need to learn complex Web Services API and allows you to use your knowledge of ADO.NET and Entity Framework. It offers an easy way to work with Salesforce data and provides high performance through advanced data caching, connection pooling, and query optimization.
In this example, we will create an SSIS package to import a CSV file with the following columns: ProductID, Name, and Price. Here are the steps to follow:
- Create a new Integration Services project.
- Drag a Data Flow Task from the Toolbox to the Control Flow tab.
- Switch to the Data Flow Task and drag a Flat File Source from the Toolbox to the Data Flow tab.
- Double-click the Flat File Source and specify the path to the CSV file. Define the columns by creating them and specifying their names and data types.
- Drag a Devart Salesforce Destination from the Toolbox to the Data Flow tab. This will be used to insert data into the Product2 table.
- Connect the Flat File Source to the Devart Salesforce Destination.
- Create a new connection manager for the Devart Salesforce Destination.
- Double-click the Devart Salesforce Destination and select the Product2 table as the Salesforce Object.
- Configure the column mappings to match the columns in the CSV file with the columns in the Product2 table.
- Drag a Conditional Split component from the Toolbox to the Data Flow tab and connect the Devart Salesforce Destination to it.
- Configure the Conditional Split component to redirect rows based on the ErrorCode column value. Rows with ErrorCode = -1 will go to the ‘Success’ output, while rows with ErrorCode > -1 will go to the ‘Error’ output.
- Drag a Devart Salesforce Source from the Toolbox to the Data Flow tab. This will be used to retrieve the ID of the pricebook we will insert prices to.
- Double-click the Devart Salesforce Source and enter a query to retrieve the ID of the standard pricebook.
- Copy the ID value and close the Devart Salesforce Source.
- Drag a Derived Column component from the Toolbox to the Data Flow tab and connect the Conditional Split to it.
- In the Derived Column component, add custom columns for PriceBook2ID, UseStandardPrice, and IsActive. Set the values for these columns based on the copied ID value and other conditions.
- Drag another Devart Salesforce Destination from the Toolbox to the Data Flow tab. This will be used to insert data into the PriceBookEntry table.
- Connect the Derived Column to the second Devart Salesforce Destination and configure the column mappings.
- Execute the package in debug mode to import the data.
And that’s it! You have successfully imported CSV data into a SQL Server database using SSIS and dotConnect for Salesforce. This approach allows for complex data import operations, even when the CSV file structure does not match the database schema.
Please share your experience with dotConnect for Salesforce and the supporting article dotConnect for Salesforce SSIS components in the comments below.