January 19, 2017 at 12:20 pm
I'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...
Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.
Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editor
January 19, 2017 at 12:25 pm
Jay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editor
If you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
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
January 19, 2017 at 12:30 pm
Phil Parkin - Thursday, January 19, 2017 12:25 PMJay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editorIf you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
Better support for JSON? Ideally I'd like to use the webservice task but this seems solely geared toward an XML payload and expects a WSDL file... unless I'm missing something
January 19, 2017 at 12:41 pm
Jay@Work - Thursday, January 19, 2017 12:30 PMPhil Parkin - Thursday, January 19, 2017 12:25 PMJay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editorIf you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
Better support for JSON? Ideally I'd like to use the webservice task but this seems solely geared toward an XML payload and expects a WSDL file... unless I'm missing something
I believe that JSON.NET is likely to give you everything you need, in terms of JSON deserializing and serializing. I've used it before in SSIS and it works well. Sounds like you prefer the ASP.NET route and that should also be fine ... choose whatever you are more comfortable with.
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
January 19, 2017 at 12:46 pm
Phil Parkin - Thursday, January 19, 2017 12:41 PMJay@Work - Thursday, January 19, 2017 12:30 PMPhil Parkin - Thursday, January 19, 2017 12:25 PMJay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editorIf you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
Better support for JSON? Ideally I'd like to use the webservice task but this seems solely geared toward an XML payload and expects a WSDL file... unless I'm missing something
I believe that JSON.NET is likely to give you everything you need, in terms of JSON deserializing and serializing. I've used it before in SSIS and it works well. Sounds like you prefer the ASP.NET route and that should also be fine ... choose whatever you are more comfortable with.
Thanks, to be honest I'd prefer SSIS, my background is far more DB but I can muddle through with C# if required. Is it easy to incorporate this json.net into the SQL data tools (or visual studio 2015 environment?) I've installed data tools extension for VS2015
January 19, 2017 at 12:51 pm
Jay@Work - Thursday, January 19, 2017 12:46 PMPhil Parkin - Thursday, January 19, 2017 12:41 PMJay@Work - Thursday, January 19, 2017 12:30 PMPhil Parkin - Thursday, January 19, 2017 12:25 PMJay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editorIf you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
Better support for JSON? Ideally I'd like to use the webservice task but this seems solely geared toward an XML payload and expects a WSDL file... unless I'm missing something
I believe that JSON.NET is likely to give you everything you need, in terms of JSON deserializing and serializing. I've used it before in SSIS and it works well. Sounds like you prefer the ASP.NET route and that should also be fine ... choose whatever you are more comfortable with.
Thanks, to be honest I'd prefer SSIS, my background is far more DB but I can muddle through with C# if required. Is it easy to incorporate this json.net into the SQL data tools (or visual studio 2015 environment?) I've installed data tools extension for VS2015
Yes it's easy, but it was 18 months ago that I last did it. If you try and fail, just post back and I'll take you through it, after reminding myself.
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
January 19, 2017 at 1:08 pm
Phil Parkin - Thursday, January 19, 2017 12:51 PMJay@Work - Thursday, January 19, 2017 12:46 PMPhil Parkin - Thursday, January 19, 2017 12:41 PMJay@Work - Thursday, January 19, 2017 12:30 PMPhil Parkin - Thursday, January 19, 2017 12:25 PMJay@Work - Thursday, January 19, 2017 12:20 PMI'mtrying to decide the best tech to use to do what I need.
I need to have a scheduled task/service that does the following...Makea call to a JSON webservice.
Take a value from that return ( a token) and use it in a call to a differentJSON webservice.
Take the dataset returned from that call, manipulate the data and pass valuesto a stored procedure for table inserts.Onthe face of it, it sounds like a job for an SSIS package but I'm finding JSONsupport in SSIS to be sketchy. Even looking at 3rd party extensions.
Would this be fairly easy to achieve with ASP.NET or should I persevere withSSIS?
I've found a couple of articles/tutorials online where SSIS is used to get JSONusing the scrip task but they are dated and the code used isn't recognized byVisual Studio 2015 e.g. Variable.MyVarName (to reference a package variable) inthe script editorIf you can write it in C#, putting it into a script component shouldn't be difficult. What do you think that ASP.NET will give you that SSIS will not?
Better support for JSON? Ideally I'd like to use the webservice task but this seems solely geared toward an XML payload and expects a WSDL file... unless I'm missing something
I believe that JSON.NET is likely to give you everything you need, in terms of JSON deserializing and serializing. I've used it before in SSIS and it works well. Sounds like you prefer the ASP.NET route and that should also be fine ... choose whatever you are more comfortable with.
Thanks, to be honest I'd prefer SSIS, my background is far more DB but I can muddle through with C# if required. Is it easy to incorporate this json.net into the SQL data tools (or visual studio 2015 environment?) I've installed data tools extension for VS2015
Yes it's easy, but it was 18 months ago that I last did it. If you try and fail, just post back and I'll take you through it, after reminding myself.
Having a bit of a problem with this. Their recommended install path is to use nuget within visual studio but when I try this via the package manager I am getting errors about default not found. My guess is that the package manager is expecting the current package to be asp.net. I can't find it via tools/extensions in VS. I have downloaded the zip file of the most recent version from newtonsoft but there are no install instructions in the readme or the site. JUst samples on how to use. Their help site contains no search results for SSIS and I've run out of ideas.
January 19, 2017 at 2:06 pm
Jay@Work - Thursday, January 19, 2017 1:08 PMHaving a bit of a problem with this. Their recommended install path is to use nuget within visual studio but when I try this via the package manager I am getting errors about default not found. My guess is that the package manager is expecting the current package to be asp.net. I can't find it via tools/extensions in VS. I have downloaded the zip file of the most recent version from newtonsoft but there are no install instructions in the readme or the site. JUst samples on how to use. Their help site contains no search results for SSIS and I've run out of ideas.
It's been a while, so I had to do some R&D before I could post back. There may be other ways, but what I ended up doing was
a) Downloading the source code
b) Opening the relevant solution (depending on target version of .NET Framework) in VS2015 and doing a build.
Once you've done this build, you will have a DLL.and that means that you can go through the process outlined on this page to use that in your SSIS projects. You will have to add the DLL for the correct version of the .NET framework to the GAC before this will work in SSIS.
Yes, I know I said it was easy. I'd forgotten all the initial faffing around which was required.
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
January 19, 2017 at 2:18 pm
Phil Parkin - Thursday, January 19, 2017 2:06 PMJay@Work - Thursday, January 19, 2017 1:08 PMHaving a bit of a problem with this. Their recommended install path is to use nuget within visual studio but when I try this via the package manager I am getting errors about default not found. My guess is that the package manager is expecting the current package to be asp.net. I can't find it via tools/extensions in VS. I have downloaded the zip file of the most recent version from newtonsoft but there are no install instructions in the readme or the site. JUst samples on how to use. Their help site contains no search results for SSIS and I've run out of ideas.
It's been a while, so I had to do some R&D before I could post back. There may be other ways, but what I ended up doing was
a) Downloading the source code
b) Opening the relevant solution (depending on target version of .NET Framework) in VS2015 and doing a build.Once you've done this build, you will have a DLL.and that means that you can go through the process outlined on this page to use that in your SSIS projects. You will have to add the DLL for the correct version of the .NET framework to the GAC before this will work in SSIS.
Yes, I know I said it was easy. I'd forgotten all the initial faffing around which was required.
Thanks for taking the time to go through this.
I've found that if I sign the assembly and then try to build the dll I get fatal errors. However if I skip the signing step (from the link) then I can build the DLL.
Before I go any further.... should I be putting this DLL in the GAC of the actual SQL Server where SSIS is running? I'm currently working on my desktop
January 19, 2017 at 2:31 pm
Jay@Work - Thursday, January 19, 2017 2:18 PMPhil Parkin - Thursday, January 19, 2017 2:06 PMJay@Work - Thursday, January 19, 2017 1:08 PMHaving a bit of a problem with this. Their recommended install path is to use nuget within visual studio but when I try this via the package manager I am getting errors about default not found. My guess is that the package manager is expecting the current package to be asp.net. I can't find it via tools/extensions in VS. I have downloaded the zip file of the most recent version from newtonsoft but there are no install instructions in the readme or the site. JUst samples on how to use. Their help site contains no search results for SSIS and I've run out of ideas.
It's been a while, so I had to do some R&D before I could post back. There may be other ways, but what I ended up doing was
a) Downloading the source code
b) Opening the relevant solution (depending on target version of .NET Framework) in VS2015 and doing a build.Once you've done this build, you will have a DLL.and that means that you can go through the process outlined on this page to use that in your SSIS projects. You will have to add the DLL for the correct version of the .NET framework to the GAC before this will work in SSIS.
Yes, I know I said it was easy. I'd forgotten all the initial faffing around which was required.Thanks for taking the time to go through this.
I've found that if I sign the assembly and then try to build the dll I get fatal errors. However if I skip the signing step (from the link) then I can build the DLL.
Before I go any further.... should I be putting this DLL in the GAC of the actual SQL Server where SSIS is running? I'm currently working on my desktop
Assuming you are working locally, do the dev on your desktop & get it working there first, then you'll have to add the assembly to the GAC of the server as part of your deployment to production.
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
February 1, 2017 at 12:27 pm
Right finally got some time to get back to this.
As far as I can tell I have successfully built the dll and pout in the GAC on my local machine.
The URL speaks of a local JSON file I think but I need to connect to a URL to return the JSON.
I'm not hugely experienced in SSIS but I managed to knock up a proof of concept in about 15 minutes when the sample webservice I created returned XML... that was before the remote party informed me they would be sending JSON.
February 2, 2017 at 9:03 am
Based on your question of choosing between SSIS and ASP.NET:
Last year I completed a task with similar target, I was using tools: VB.NET (windows application), RestSharp and Newtonsoft.Json. My target was to create a scheduled task running every hour to grab new data from JSON Services to import into my DB, and also export from my DB to JSON Services.
If your logic of mapping data is simple, SSIS would be better, else I prefer you spending more time on ASP.NET which is more easy to program, debug, and log at your purpose.
February 2, 2017 at 12:11 pm
It's a bit of both... I can get the results from the webservice and into a variable in a few lines of C# but the data manipulation and passing to stored procedures would take a fair amount of code.
On the flip side the data part would be a breeze using SSIS but the simple call to get the JSON is proving a nightmare for me.
If there was a built in SSIS task that supported JSON webservices that would be perfect, but as there isn't I need to get this into SSIS
WebRequest request = WebRequest.Create("https://xxxx");
request.Method = "GET";
request.ContentType = "application/json; charset=utf-8";
using (Stream s = request.GetResponse().GetResponseStream())
{
using (StreamReader sr = new StreamReader(s))
{
var jsonData = sr.ReadToEnd();
}
}
February 2, 2017 at 12:17 pm
Jay@Work - Thursday, February 2, 2017 12:11 PMIt's a bit of both... I can get the results from the webservice and into a variable in a few lines of C# but the data manipulation and passing to stored procedures would take a fair amount of code.
On the flip side the data part would be a breeze using SSIS but the simple call to get the JSON is proving a nightmare for me.
If there was a built in SSIS task that supported JSON webservices that would be perfect, but as there isn't I need to get this into SSISWebRequest request = WebRequest.Create("https://xxxx");
request.Method = "GET";
request.ContentType = "application/json; charset=utf-8";
using (Stream s = request.GetResponse().GetResponseStream())
{
using (StreamReader sr = new StreamReader(s))
{
var jsonData = sr.ReadToEnd();
}
So what part are you stuck on, exactly?
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
February 2, 2017 at 12:21 pm
How do I replicate the above in an SSIS package?
In asp.net I just add some using statements for some nampespaces and I'm away but in a package I'm guessing I'm going to need a connection to this address and when I tried previously in script it complained about no WSDL files and other errors that I now forget.
So in short, call (connect) to the address above. Parse the payload and put each tag value into a variable.
I will then pump those variables into a SQL table after some massaging (I can do this bit easy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply