Revision as of 10:01, 18 June 2018

Please note that this page is currently under construction. There is more information to come.

Overview

An example on how to import SmartSimple OData Report into MS SQL Database using SQL Server Integration Services (SSIS).

Configuration - Essentials

1. SQL Server
2. Visual Studio 2015/2017
3. SQL Server Integration Services (SSIS) VS2015/2017 add-on

Creating a new SSIS Package

A simple SSIS package

1. Open Visual Studio
2. Create a new Integration Service Project
3. Save the new project into the desired folder
4. Drag a dataflow task to the working surface
5. Double click "Data Flow" to bring up the new "Data Flow" work surface
6. Select "OData Source" and drag to the work surface
7. Double click "OData Source", OData Source Editor appears
   1. Click "New", a new window "OData Connection Manager Editor" opens
   2. Enter the URL of the OData, username, password
   3. Click "Test Connection" to test
   4. Press "OK"
   5. "OData Connection Manager Editor" closes
   6. Choose the desired collection
   7. Click "Preview", a new window will pop up to show data from the collection
   8. Click "Columns"
   9. Choose the column headers you want to transfer to MSSQL
   10. Press "OK"
8. Select "Destination Assistant" to set up SQL Server Connection
9. Double click "Destination Assistant", "OLE DB Destination Editor" appears
10. Select the SQL Server to import OData to
11. Connect "OData Source" and "OLE DB Destination"
12. Double click "OLE DB Destricaiton" to edit metadata if necessary, then press "OK"
13. Press "Start" to run the package
14. When process is complete, it will show you how many rews were imported to MSSQL database

Deploying SSIS Package

Scheduling SSIS Package

See Also

• Reports
• OData Overview
• OData Connector
• SmartSimple OData Feed to Excel