Difference between revisions of "SmartSimple OData Feed to SQL Server Database"

From SmartWiki
Jump to: navigation, search
(Created page with "=Overview= =Configuration - Essentials= =Configuration - Advanced= =Appendix= ==Options and Settings== <!--Explain all applicable misc options and settings that shouldn't be e...")
 
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
=Overview=
+
{{ Banner-UnderConstruction }}
−
=Configuration - Essentials=
 
−
=Configuration - Advanced=
 
−
=Appendix=
 
−
==Options and Settings==
 
−
<!--Explain all applicable misc options and settings that shouldn't be explained above here...-->
 
−
===General Settings===
 
−
{| class="wikitable"
 
−
|-
 
−
||Option
 
−
||Description
 
−
||Example
 
−
|-
 
−
||
 
−
||
 
−
||
 
−
|}
 
  
−
===Display Settings===
 
−
{| class="wikitable"
 
−
|-
 
−
||Option
 
−
||Description
 
−
||Example
 
−
|-
 
−
||
 
−
||
 
−
||
 
−
|}
 
  
−
=Examples=
 
−
<!--
 
−
 
−
Sample Image Format:
 
−
 
−
[[File:xmlSample.png|thumb|none|middle|500px|Example field configured as a budget sheet.]]
 
−
 
−
 
−
 
−
Sample Table Format:
 
−
 
−
{| class="wikitable"
 
−
| Option
 
−
| Description
 
−
| Example
 
−
|-
 
−
|
 
−
|
 
−
|
 
−
|}
 
−
 
−
 
−
 
−
Sample Syntax Format:
 
−
Empty space at start of line to highlight the row, italicize any variables that the user would fill in (as opposed to literal words that need to be used in syntax)
 
−
 
−
@section.''SectionNodeName''@
 
−
 
−
 
−
 
−
Sample Code Block:
 
−
When you want to write an example block of code, you should highlight it and also escape any special characters that might be construed as HTML formatting and outputted incorrectly by the Wiki by using a pre (pre-formatted text) tag
 
−
 
−
<pre>
 
−
<?xml version="1.0"?>
 
−
<Company>
 
−
<Name>Oranges Inc.</Name>
 
−
<Description>Peel.
 
−
Eat.
 
−
Repeat.</Description>
 
−
</Company>
 
−
<Company>
 
−
<Name>Bananas Ltd.</Name>
 
−
<Description>You're going to go "bananas" over our product!</Description>
 
−
</Company>
 
−
</pre>
 
−
 
−
 
−
 
−
Unordered list:
 
−
When listing information with bullet points in no order
 
−
 
−
* Point 1
 
−
* Point 2
 
−
* Point 3
 
−
 
−
 
−
 
−
Ordered list:
 
−
When listing information with numbers to denote ordered steps
 
−
 
−
# Step 1
 
−
# Step 2
 
−
# Step 3
 
  
 +
=Overview=
 +
An example on how to import SmartSimple OData Report into MS SQL Database using SQL Server Integration Services (SSIS).
 +
[[Image:TranformDataDiagram.png|thumb|center|1000x400px]]
  
 +
=Configuration - Essentials=
 +
# SQL Server
 +
# Visual Studio 2015/2017
 +
# SQL Server Integration Services (SSIS) VS2015/2017 add-on
  
−
Sample Page Header Banners:
+
=Creating a new SSIS Package=
−
Deprecated Feature
+
==A simple SSIS package==
−
<span class="mceNonEditable template" id="bs_template:@@@TPL0@@@" data-bs-name="Deprecated" data-bs-type="template" data-bs-id="0">{{ Deprecated }}</span>
+
# Open Visual Studio
−
Deprecated Page
+
# Create a new Integration Service Project
−
<span class="mceNonEditable template" id="bs_template:@@@TPL1@@@" data-bs-name="DeprecatedPage" data-bs-type="template" data-bs-id="1">{{ DeprecatedPage }}</span>
+
# Save the new project into the desired folder
−
Professional Services
+
# Drag a dataflow task to the working surface
−
<span class="mceNonEditable template" id="bs_template:@@@TPL2@@@" data-bs-name="Banner-Billable" data-bs-type="template" data-bs-id="2">{{ Banner-Billable }}</span>
+
# Double click "Data Flow" to bring up the new "Data Flow" work surface
−
Page Under Construction
+
# Select "OData Source" and drag to the work surface
−
<span class="mceNonEditable template" id="bs_template:@@@TPL3@@@" data-bs-name="Banner-UnderConstruction" data-bs-type="template" data-bs-id="3">{{ Banner-UnderConstruction }}</span>
+
# Double click "OData Source", OData Source Editor appears
 +
## Click "New", a new window "OData Connection Manager Editor" opens
 +
## Enter the URL of the OData, username, password
 +
## Click "Test Connection" to test
 +
## Press "OK"
 +
## "OData Connection Manager Editor" closes
 +
## Choose the desired collection
 +
## Click "Preview", a new window will pop up to show data from the collection
 +
## Click "Columns"
 +
## Choose the column headers you want to transfer to MSSQL
 +
## Press "OK"
 +
# Select "Destination Assistant" to set up SQL Server Connection
 +
# Double click "Destination Assistant", "OLE DB Destination Editor" appears
 +
# Select the SQL Server to import OData to
 +
# Connect "OData Source" and "OLE DB Destination"
 +
# Double click "OLE DB Destricaiton" to edit metadata if necessary, then press "OK"
 +
# Press "Start" to run the package
 +
# 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 Overview]]
 +
* [[OData Connector]]
 +
* [[SmartSimple OData Feed to Excel]]

Latest revision as of 11:01, 18 June 2018


Construction warning.png 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).

TranformDataDiagram.png

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

Retrieved from ‘https://wiki.smartsimple.com/index.php?title=SmartSimple_OData_Feed_to_SQL_Server_Database&oldid=32141’