June 17, 2016 at 8:44 am
Hi!
I have to consume a Rest API where we have our survey data. Here I want to save the data in my database and update the data every night. Now I see different ways to realize this and do not know which way to go.
1. I can create a Script Component within SSIS to consume the Rest API and get the data into the columns. Here I have the problem that I do not have a fix number of columns the range is up to 300. I would have to set up 300 columns. Another problem is that I have a few dlls to add to the GAC. In the end I would schedule this process with the sql agent.
2. I create a .NET Application to consume the Rest API and grab the data. These application could spit out the data into e.g. a flat file. So I could a Exectue Process Task in SSIS and consume the data with a data flow task. Here I would use the sql agent as well.
3. Sine I have to start the process every night I could also use a windows service that starts a .NET App that starts the SSIS Package.
Since I am new to SSIS and these kind of architecture questions I would really appreciate any recommendation on what way I should prefer.
br!
June 17, 2016 at 8:54 am
neophilius (6/17/2016)
Hi!I have to consume a Rest API where we have our survey data. Here I want to save the data in my database and update the data every night. Now I see different ways to realize this and do not know which way to go.
1. I can create a Script Component within SSIS to consume the Rest API and get the data into the columns. Here I have the problem that I do not have a fix number of columns the range is up to 300. I would have to set up 300 columns. Another problem is that I have a few dlls to add to the GAC. In the end I would schedule this process with the sql agent.
2. I create a .NET Application to consume the Rest API and grab the data. These application could spit out the data into e.g. a flat file. So I could a Exectue Process Task in SSIS and consume the data with a data flow task. Here I would use the sql agent as well.
3. Sine I have to start the process every night I could also use a windows service that starts a .NET App that starts the SSIS Package.
Since I am new to SSIS and these kind of architecture questions I would really appreciate any recommendation on what way I should prefer.
br!
Good questions. An SSIS Script Component Source could consume the API source data for you, and the fact that you get to keep all of the processing in one place keeps things tidy, IMO. Adding DLLs to the GAC is nothing to be worried about.
But this is not exactly a 'hello world' SSIS package for a newbie, so that is also a consideration. You'll find loading a text file far easier to complete in SSIS than using a Script Component Source, and it may be that this would be a faster solution for you, given your lack of SSIS experience.
--edit: fixed typo
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2016 at 4:40 am
Thanks for your advice. It is always difficult to decide such elemental things when you are not an expert.
There is on thing I would like to ask. I set up a Console application to get the data from my Rest API save these data into XX and load it with SSIS to get it into my database. What format would you use to substitute the XX?
At the moment I download a Json file and map this with Classes which hold the data.
Thanks!
June 21, 2016 at 6:04 am
If you choose a pipe-delimited flat file, you won't go too far wrong (assuming there is no possibility of a pipe (|) appearing in the data you are loading.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2016 at 7:20 am
Personally I find PowerShell well suited for something like this. However, it can be a bit tricky especially if you are new to it.
June 21, 2016 at 7:39 am
I use Python for this personally. Python handles this very well and is easier to maintain than .NET apps that sometimes just overcomplicates things. It works well in all environments and SSIS or SQL can fully control it or work independently.
Examples
I work in digital marketing, so using the Google API is common for me. I use Python with OAuth to authenticate, query, transform and write data to CSV independently on a secondary Windows environment (AppServer). Then SSIS is setup to just check a directory for data dumps and imports it into the database.
Another option is similar to above, but instead of the Python script running independently, the Python script is converted to command line tool where SQL Server or SSIS can fully control it by passing parms.
As JSON is a common output, using Python to clean and convert JSON is also why I use it with SQL Server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply