posts - 88 , comments - 3 , trackbacks - 0

Tuesday, June 16, 2015

Connect SharePoint to SQL Server through SSIS

Using SQL Server as a backup for critical business data provides an essential safety net against loss. In addition, backing up data to SQL Server enables business users to more easily connect that data with features like reporting, full-text search, analytics, and more.

This example demonstrates how to use the SharePoint ADO.NET Data Provider inside of a SQL Server SSIS workflow to transfer data directly from SharePoint and/or SharePoint Online into a Microsoft SQL Server database. The exact same procedure outlined below can be used with any CData ADO.NET Data Providers to connect SQL Server directly with remote data via SSIS.

  1. Open Visual Studio and create a new Integration Services Project.
  2. Add a new Data Flow Task from the Toolbox onto the Control Flow screen.
  3. In the Data Flow screen, add a DataReader Source and an OLE DB Destination from the Toolbox.
  4. Add a new Data Connection, and select your provider as .NET Providers\CData SharePoint Data Provider.
  5. In the connection manager, enter the connection details. This example uses a SharePoint blog page.
  6. Open the DataReader editor and set the following information:
    • ADO.NET connection manager: In the Connection Managers menu, select the Data Connection you just created.
    • Data access mode: Select 'SQL command'.
    • SQL command text: In the DataReader Source editor, open the Component Properties tab and enter a SELECT command, such as the one below:
      SELECT * FROM Posts
  7. Close the DataReader editor and drag the arrow below the DataReader Source to connect it to the OLE DB Destination.
  8. Open the OLE DB Destination and enter the following information in the Destination Component Editor.

    • Connection manager: Add a new connection. Enter your server and database information here. In this example, SQLExpress is running on a separate machine.
    • Data access mode: Set your data access mode to "table or view" and select the table or view to populate in your database.
  9. Configure any properties you wish to on the Mappings screen.
  10. Close the OLE DB Destination Editor and run the project. After the SSIS Task has finished executing, your database will be populated with data obtained from SharePoint.

SSIS Sample Project

To get started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional sample project.

Note: Before running the demo, you will need to change your connection details to fit your environment.

Posted On Tuesday, June 16, 2015 4:10 AM | Filed Under [ ado.net sharepoint cdata ssis SQL server ]

Replicate PreEmptive Analytics Data from PowerShell

Write a quick PowerShell script to query PreEmptive Analytics data. Use connectivity to the live data to replicate PreEmptive Analytics data to SQL Server.

The CData ODBC Driver for PreEmptive Analytics enables out-of-the-box integration with Microsoft's built-in support for ODBC. The ODBC driver instantly integrates connectivity to the real PreEmptive Analytics data with PowerShell.

You can use the .NET Framework Provider for ODBC built into PowerShell to quickly automate integration tasks like replicating PreEmptive Analytics data to other databases. This article shows how to replicate PreEmptive Analytics data to SQL Server in 5 lines of code.

You can also write PowerShell code to execute create, read, update, and delete (CRUD) operations. See the examples below.

Create a DSN

If you have not already done so, specify connection properties defined in the data source name (DSN). Typical connection properties are the following:

  • User
  • Password
  • AuthScheme
  • URL

You can configure the DSN in the built-in Microsoft ODBC Data Source Administrator. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to use the Microsoft ODBC Data Source Administrator to create and configure a DSN.

Connect to PreEmptive Analytics

The code below shows how to use the DSN to initialize the connection to PreEmptive Analytics data in PowerShell:

$conn New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=CData PA Source x64"

Back Up PreEmptive Analytics Data to SQL Server

After you enable caching, you can use the code below to replicate data to SQL Server.

Set the following connection properties to configure the caching database:

  • CacheProvider: The name of the ADO.NET provider. This can be found in the Machine.config for your version of .NET. For example, to configure SQL Server, enter System.Data.SqlClient.

  • CacheConnection: The connection string of properties required to connect to the database. Below is an example for SQL Server:

    Server=localhost;Database=RSB;User Id=sqltest;Password=sqltest;

The SQL query in the example can be used to refresh the entire cached table, including its schema. Any already existing cache is deleted.

$conn.Open()
# Create and execute the SQL Query
$SQL "CACHE DROP EXISTING SELECT * FROM " $FeaturesSummary
$cmd New-Object System.Data.Odbc.OdbcCommand($sql,$conn)
$count $cmd.ExecuteNonQuery()
$conn.Close()

The driver gives you complete control over the caching functionality. See the help documentation for more caching commands and usage examples. See the help documentation for steps to replicate to other databases.

Other Operations

To execute any SQL command to an ODBC data source in PowerShell, initialize the command and then call ExecuteNonQuery. Below are some more example CRUD commands to PreEmptive Analytics through the .NET Framework Provider for ODBC:

Retrieve PreEmptive Analytics Data

$sql="SELECT Count, FeatureName from FeaturesSummary"
 
$daNew-Object System.Data.Odbc.OdbcDataAdapter($sql$conn)
$dtNew-Object System.Data.DataTable
$da.Fill($dt)
 
$dt.Rows | foreach {
$dt.Columns | foreach ($col in dt{
Write-Host $1[$_]
}
}
$sql="SELECT Count, FeatureName from FeaturesSummary"
 
$daNew-Object System.Data.Odbc.OdbcDataAdapter($sql$conn)
$dtNew-Object System.Data.DataTable
$da.Fill($dt)
 
$dt.Rows | foreach {
Write-Host $_.count $_.featurename
}

Update PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("UPDATE FeaturesSummary SET Time='01/01/2015' WHERE Id = @myId"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Insert PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("INSERT INTO FeaturesSummary SET Time='01/01/2015' WHERE Id = @myId"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Delete PreEmptive Analytics Data

$cmd New-Object System.Data.Odbc.OdbcCommand("DELETE FROM FeaturesSummary WHERE Id = @myid"$conn)
$cmd.Parameters.Add(new System.Data.Odbc.OdbcParameter("myId","001d000000YBRseAAH")
$cmd.ExecuteNonQuery()

Posted On Tuesday, June 16, 2015 4:02 AM | Filed Under [ cdata ODBC powershell PreEmptive Analytics ]

Connect to and Query Eloqua Data in QlikView over ODBC

Create data visualizations with Eloqua data in QlikView.

The CData ODBC drivers expand your ability to work with data from a wide variety of data sources. This article outlines simple steps to connect to Eloqua data and create data visualizations in QlikView.

Populate a Chart with Eloqua Data

The steps below supply the results of an SQL query to a visualization in QlikView. In this article, you will create a bar chart with the query below:

SELECT Name, ActualCost FROM Campaign
  1. If you have not already, specify connection properties in a DSN (data source name). You can use the Microsoft OBC Data Source Administrator to create and configure ODBC DSNs. Typical connection properties are the following:

    • User
    • Password
    • Company

    See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

  2. Click Edit Scripts and add the connection. You can select the DSN on the Data tab in the Edit Scripts wizard.
  3. After selecting the DSN, click Select to build the query in the SELECT statement wizard. Or, you can enter the query directly into the script.
    A script that connects and executes an SQL query. (Salesforce is shown.)
  4. Create a new bar chart with Name and ActualCost as the x- and y-axes using the Create Chart wizard.
  5. Finish the wizard to generate the chart.

    A chart populated with the results of a query. (Salesforce is shown.)

Posted On Tuesday, June 16, 2015 3:52 AM | Filed Under [ cdata ODBC Eloqua QlikView ]

Use Crystal Reports to create reports with Salesforce data

Crystal Reports provides built-in support for the JDBC standard. The CData JDBC Driver for Salesforce enables you to use the Report Wizard and other tools to access Salesforce data in Crystal Reports. This article shows how to create a simple report that features Salesforce data.

Install the JDBC Driver

Install the CData JDBC Driver for Salesforce by including the driver JAR in the Crystal Reports classpath: Set the DataDriverCommon element in the CRConfig.xml file to the path to the JAR.

The CRConfig.xml is usually located at C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\java — the path might be slightly different based on your installation. The driver JAR is located in the lib subfolder of the installation directory.

Connect To Salesforce Data

Follow the procedure below to use the JDBC connection wizard to connect.

  1. In a new, blank report, click Create New Connection->JDBC (JNDI).
  2. In the wizard, select the JDBC connection URL:

    jdbc:salesforce:User=username;Password=password;AccessToken=Your_Access_Token;
  3. Set the driver class name:

    cdata.jdbc.salesforce.SalesforceDriver

Design a Report

After connection a JDBC connection to Salesforce, you can then use the Report Wizard to add Salesforce data to your report.

  1. Configure the data source by selecting the tables and fields needed in the report.

  2. Configure the chart type. For example, create a Bar Chart that aggregates the values in the Name column. After naming the chart, select Account.Name from the 'On change of' menu. In the 'Show summary' menu, select the SUM function and Account.AnnualRevenue.

  3. Configure other filters and the report template, as needed.

Preview the finished report to view the chart, populated with your data. If you want to filter out null values, use a SelectionFormula.

Posted On Tuesday, June 16, 2015 2:09 AM | Filed Under [ salesforce cdata data reports Crystal Reports ]

Powered by: