Salesforce Source through SSIS

SSIS and Import/Export
1

Hi,
Is anyone can advice me how to pull the data from Salesforce through SSIS?
I know I can use CozyRoc, and another third party adds on for SSIS. However, I want to use SSIS (Script task or another way). Any help would be much appreciated.

Thanks.

2

I would recommend this resource.
You could check out the piece for c# and implement in ssis script task
Our company just bought sf and need to look into this myself but probably going to go the python or node route

3

What does a SalesForce file look like?

4

sales force provides rest api you can access via different SDKs

5

Ok... I'm not a programmer so didn't know much about REST. I watched a couple of 'tubes on it and read a couple of articles and now I get it. It can be pretty much whatever you want it to be. It also explains why MS thought it was so important to add JSON to it's repertoire. JSON uses NVPs unless you're using an array and has formatting requirement as string as true/real CSV instead of the joke that most people refer to as CSV.

Now knowing just enough to be dangerous, my question, why can't you just fire up the REST API and have it generate a file, look at it, and figure out what it would take to parse and import it into tables? From what I understand, there's a fair bit that can be done through various tasks in SSIS. Can't one of them be a call to the REST API to produce a file that you can then turn around and import?

To be sure, those are questions on my part.

Ah... and one more question... can you fire REST APIs to produce a file from a command line interface? If so, I personnaly wouldn't even bother with SSIS.

6

If anyone can understand it is you @JeffModen

When you hit an api you can turn around and feed the json response directly to your staging table without saving to file

You can hit an api and produce a file with cli tools like curl

You dont need ssis u could use powershell python.

You can even call a rest api from within tsql

Let me put together a demo of each options using a public rest api

7

Thanks yosiasz for your help. As you know to introduce new things or new processes/Python not easy for all approval. Company will not allow on third-party tools because of PHI data. In my previous Exp, I used third party SF connector through SSIS but here I can't. I would love to see the demo for me to work on the REST API.

8

What do you mean by third party tools? Like node and python? How about powershell?

9

I meant CozyRoc/cdata for SSIS. Yes, Powershell should be Okay.

10

what kind of authentication are you using (oAuth, sso, etc) ? do you have a sandbox/dev/qa instance?

11

Using Powershell to call a rest api, so this just shows you the principles of how you could do it using powershell. There are tons of ways of doing it with powershell this is just 1 of tons of samples.

first in sql server just to play around with this so you can get an idea that it does work.

create table SalesForceStaging
(
	bib_key nvarchar(max),
	preview nvarchar(max),
	thumbnail_url nvarchar(max),
	preview_url nvarchar(max),
	info_url nvarchar(max)
)

Then create a powershell script and save it anywhere and then run it from withing powershell. Once you are familiar with this and are comfortable you can move on implementing it for SalesForce. But for SF it will be a bit more meatier because you will have to first get the token and then make the call you need to fetch the data you need.

We will deal with that once you try this here sample and get comfortable with it.


$url = "https://openlibrary.org/api/books?bibkeys=ISBN:0201558025&format=json"

#Invoke-Webrequest -Uri $url | ConvertFrom-Json 

$json = (Invoke-Webrequest -Uri $url).Content

$x = $json | ConvertFrom-Json

$data = $x."ISBN:0201558025"

#to see what is returned
$data

foreach($d in $data)
{
	
	$bib_key=$d.bib_key
	$preview=$d.preview
	$thumbnail_url=$d.thumbnail_url
	$preview_url=$d.preview_url
	$info_url=$d.info_url

	
$insertquery=" 
INSERT INTO [dbo].[SalesForceStaging] 
           ([bib_key] 
           ,[preview] 
           ,[thumbnail_url]
		   ,preview_url
		   ,info_url) 
     VALUES 
           ('$bib_key'
           ,'$preview'
           ,'$thumbnail_url'
		   ,'$preview_url'
		   ,'$info_url'
		   ) 
GO 
" 
#here the final insert into the database you will have 
#to enter your own sql server name instead of  'localhost\apps'
Invoke-SQLcmd -ServerInstance 'localhost\apps' 
-query $insertquery -Database sqlteam 	
}
12

using python (for those curious) Just think of the implementation languages as an wooden hammer or an iron hammer or an aluminum hammer or a rubber hammer.

under the hood they all pretty much do the same thing, drive the nail

import pyodbc 
import requests

conn = pyodbc.connect("Driver={SQL Server};"               
               "Server=localhost\\apps;"
               "Database=sqlteam;"
               "Trusted_Connection=yes;")
cursor = conn.cursor()

url = "https://openlibrary.org/api/books?bibkeys=ISBN:0201558025&format=json"

resp = requests.get(url)

if resp.status_code != 200:
    # This means something went wrong.
    raise ApiError('GET /books/ {}'.format(resp.status_code))
a_dict = resp.json();

for key in a_dict:
    cursor.execute("""
    INSERT INTO dbo.SalesForceStaging(bib_key,preview,thumbnail_url,preview_url,info_url)  
    VALUES (?,?,?,?,?)""",
    a_dict[key]['bib_key'], 
    a_dict[key]['preview'], 
    a_dict[key]['thumbnail_url'], 
    a_dict[key]['preview_url'], 
    a_dict[key]['info_url']) 
    conn.commit()
13

Hi Yosiasz,

I apologize for the late reply. I got sick and on time off for next week. I will play with the sample once I back in the office/online.

I appreciate your help.
Stay Safe.
Thanks.

1 Like