tag:blogger.com,1999:blog-4358489820319105877.post7769389526836355864..comments2024-03-21T04:57:05.394-07:00Comments on Dennis and Jim's Data Engineering Blog: Using a JSON Feed as a Data Source in SSISJimhttp://www.blogger.com/profile/08462849367548283573noreply@blogger.comBlogger245125tag:blogger.com,1999:blog-4358489820319105877.post-70035400934188874772021-09-30T04:27:37.443-07:002021-09-30T04:27:37.443-07:00Hey, thanks for the blog article.Really thank you!...Hey, thanks for the blog article.Really thank you! Great.<br /><a href="https://onlineitguru.com/data-science-course.html" rel="nofollow">Data Science Course Online</a><br /><a href="https://onlineitguru.com/data-science-course.html" rel="nofollow">Data Science Certification Course</a>lakshmibhucynixhttps://www.blogger.com/profile/09244438858315114254noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-73586675080953522732021-07-06T22:30:33.437-07:002021-07-06T22:30:33.437-07:00Thanks for the amazing post.
SQL Server Load Soap ...Thanks for the amazing post.<br /><a href="https://zappysys.com/blog/import-rest-api-json-sql-server/" rel="nofollow">SQL Server Load Soap Api</a>Chris Lesnarhttps://www.blogger.com/profile/00483876665824529326noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-6187909973430207742021-05-06T10:58:36.315-07:002021-05-06T10:58:36.315-07:00Continuaton of Code:
try
{
NP...<br /><br /><br /><br /> <br /><br />Continuaton of Code:<br />try<br /> {<br /><br /> NPSResponse[] outPutMetrics = GetWebServiceResult(wUrl);<br /><br /> //For each group of metrics output records<br /> foreach (var metric in outPutMetrics)<br /> {<br /> Output0Buffer.AddRow();<br /> Output0Buffer.NPSAuthor = metric.author;<br /> Output0Buffer.NPSCreateDate = metric.createdAt;<br /> Output0Buffer.NPSAuthorId = metric.id;<br /> Output0Buffer.Ltr = metric.ltr;<br /> Output0Buffer.NPSResponse = metric.response;<br /> Output0Buffer.UID = metric.uid;<br /><br /> }<br /><br /> }<br /> catch (Exception e)<br /> {<br /> FailComponent(e.ToString());<br /> }<br /><br /> }<br /><br /> private NPSResponse[] GetWebServiceResult(string wUrl)<br /> {<br /><br /> HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);<br /> httpWReq.Accept = "application/json";<br /> httpWReq.Headers["Authorization"] = Variables.APITOKEN;<br /> HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();<br /> NPSResponse[] jsonResponse = null;<br /><br /> try<br /> {<br /> //Test the connection<br /> if (httpWResp.StatusCode == HttpStatusCode.OK)<br /> {<br /><br /> Stream responseStream = httpWResp.GetResponseStream();<br /> string jsonString = null;<br /><br /> //Set jsonString using a stream reader<br /> using (StreamReader reader = new StreamReader(responseStream))<br /> {<br /> jsonString = reader.ReadToEnd().Replace("\\", "");<br /> reader.Close();<br /> }<br /><br /> //Deserialize our JSON<br /> JavaScriptSerializer sr = new JavaScriptSerializer();<br /> //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly<br /> //The JSON here is serialized weird, normally you would not need this trim<br /> jsonResponse = sr.Deserialize(jsonString.Trim('"'));<br /><br /> }<br /> //Output connection error message<br /> else<br /> {<br /> FailComponent(httpWResp.StatusCode.ToString());<br /><br /> }<br /> }<br /> //Output JSON parsing error<br /> catch (Exception e)<br /> {<br /> FailComponent(e.ToString());<br /> }<br /> return jsonResponse;<br /><br /> }<br /><br /> private void FailComponent(string errorMsg)<br /> {<br /> bool fail = false;<br /> IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;<br /> compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);<br /><br /> }<br /><br />}<br />#endregion<br /><br />#region JSON Class<br />//Class to hold our work group metrics<br />class NPSResponse<br />{<br /> public string author { get; set; }<br /> public DateTime createdAt { get; set; }<br /> public string id { get; set; }<br /> public int ltr { get; set; }<br /> public string response { get; set; }<br /> public string uid { get; set; }<br />}<br />#endregion<br /><br />ChMelimihttps://www.blogger.com/profile/05571014816111146840noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-60738915429926997962021-05-06T10:57:14.755-07:002021-05-06T10:57:14.755-07:00Hi Jim,
My json2csharp is showing below:
publ...Hi Jim,<br /><br />My json2csharp is showing below:<br /> public class Root<br /> {<br /> public string author { get; set; }<br /> public DateTime createdAt { get; set; }<br /> public object id { get; set; }<br /> public object ltr { get; set; }<br /> public object response { get; set; }<br /> public string uid { get; set; }<br /> }<br />I am getting the following error:<br />[Error Getting Data From Webservice!] Error: System.MissingMethodException: No parameterless constructor defined for type of 'NPSResponse[]'.<br /> at System.Web.Script.Serialization.ObjectConverter.ConvertDictionaryToObject(IDictionary`2 dictionary, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)<br /> at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeInternal(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)<br /> at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)<br /> at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize(JavaScriptSerializer serializer, String input, Type type, Int32 depthLimit)<br /> at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)<br /> at ScriptMain.GetWebServiceResult(String wUrl)<br /><br />My code is as follows:<br /><br /> public override void CreateNewOutputRows()<br /> {<br /> //Get SSIS Variables<br /> int pagesize = Variables.pagesize;<br /> <br /> //Set Webservice URL<br /> string wUrl = "https://api.podium.com/api/v2/locations/35051/nps?pagesize=" + pagesize;<br /><br /> ChMelimihttps://www.blogger.com/profile/05571014816111146840noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-72942157836568815772021-03-15T09:59:34.714-07:002021-03-15T09:59:34.714-07:00I gained new knowledge from well written content o...I gained new knowledge from well written content of this blog. It is showing some different kind of strategy to keep work better and improve with every new assignment. Gracefully written blog<a href="https://newscutzy.com/discord/discord-javascript-error/" rel="nofollow">How to get rid of Discord Javascript Error [100% SOLVE ISSUE]</a>markjackhttps://www.blogger.com/profile/03866766387537914755noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-17438058401392465082020-08-19T02:28:10.430-07:002020-08-19T02:28:10.430-07:00 Nice blog ! Here i found how to get a JSON feed i... Nice blog ! Here i found how to get a JSON feed into our data warehouse, using SSIS. Keep sharingStädade Hemhttps://www.blogger.com/profile/06695994706699360183noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-42110198658542029202020-06-10T03:04:11.865-07:002020-06-10T03:04:11.865-07:00I think SQL and other tools are best for this.
ta...I think SQL and other tools are best for this.<br /><br /><a href="https://zappysys.com/blog/import-rest-api-tableau-read-json-soap-xml-csv/" rel="nofollow">tableau rest api connection</a>James Zicrovhttps://www.blogger.com/profile/00094356083987556970noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-53741339020986412172020-03-17T17:16:36.503-07:002020-03-17T17:16:36.503-07:00Hi This is very helpful, thank you!I have a questi...Hi This is very helpful, thank you!I have a question and I tried everything but couldn't solve - I'm trying to load the data from rest api as well, the data I'm trying to load is very large and the sql server truncates most of the data. I'm loading the data from rest api to a variable and writing that variable in the database. Is there a possibility to have it iterate through in the script itself? Note- I tried to de-serialize the Json file using NewtonSoft.Json. But when I close the edit script page the installation (Newtonsoft.json) was no longer available and throws an error when executing the script. Can you please helpPavhttps://www.blogger.com/profile/03735460331921053905noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-81335986169179706502019-12-01T13:48:53.878-08:002019-12-01T13:48:53.878-08:00You probably have your data type set to an integer...You probably have your data type set to an integer on your output buffer. Jimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-69138053635014895242019-12-01T13:46:27.184-08:002019-12-01T13:46:27.184-08:00This usually happens when you are attempting to as...This usually happens when you are attempting to assign a field to a "text stream [DT_TEXT]" data type. You would need to change your code to:<br />if (metric.Changetype != null)<br /> {<br /> Output0Buffer.ChangeType.AddBlobData(Encoding.ASCII.GetBytes(metric.Changetype));<br /> }<br /> else<br /> {<br /> Output0Buffer.ChangeType.SetNull();<br /> }<br /><br />or this for unicode:<br /><br />if (metric.Changetype != null)<br /> {<br /> Output0Buffer.ChangeType.AddBlobData(Encoding.Unicode.GetBytes(metric.Changetype));<br /> }<br /> else<br /> {<br /> Output0Buffer.ChangeType.SetNull();<br /> }<br /> Jimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-17312189817061986482019-11-22T16:18:24.937-08:002019-11-22T16:18:24.937-08:00Hi Jim,
I'm getting an "Property or Ind...Hi Jim, <br /><br />I'm getting an "Property or Indexer cannot be assigned to -- it is read only" <br /><br /> try<br /> {<br /> //Call getWebServiceResult to return our WorkGroupMetric array<br /> WorkGroupMetric[] outPutMetrics = GetWebServiceResult(wUrl);<br /><br /> //For each group of metrics output records<br /> foreach (var metric in outPutMetrics)<br /> {<br /> Output0Buffer.AddRow();<br /> Output0Buffer.ChangeType = metric.Changetype;<br /><br /> }<br /><br /> }Anonymoushttps://www.blogger.com/profile/01075279765986885576noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-82992986033920887092019-10-30T13:53:22.741-07:002019-10-30T13:53:22.741-07:00did anyone called API which is using DotNetOpenAut...did anyone called API which is using DotNetOpenAuth in SSIS script task?udayhttps://www.blogger.com/profile/01626087366104150488noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-29888905364268881662019-10-22T10:43:55.731-07:002019-10-22T10:43:55.731-07:00Sure email me jamesferrisjr at gmailSure email me jamesferrisjr at gmailJimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-68551288227225732302019-10-22T10:42:25.273-07:002019-10-22T10:42:25.273-07:00Sure email me at jamesferrisjr at gmailSure email me at jamesferrisjr at gmailJimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-14390647995274052962019-10-17T03:58:32.846-07:002019-10-17T03:58:32.846-07:00Hi Jim,
are you still willing to help in the issu...Hi Jim,<br /><br />are you still willing to help in the issues connected with this code (it is very old topic so I am not sure)? And even your code looks great and I do all steps I hit to the wall with error :| Will explain more and I will paste a code if you are still here ;) <br /><br />Regards,<br />BartBarthttps://www.blogger.com/profile/06425291544601479863noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-11426612834718661612019-10-07T09:32:05.432-07:002019-10-07T09:32:05.432-07:00First thank you for this example! It is very clos...First thank you for this example! It is very close to what I need to accomplish. Not sure if you're still answering questions on this post, but I get an error "System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host."<br />I'm not sure why the connection gets immediately terminated. If I hit the endpoint URL in the browser it works perfectly.AnonPlayerhttps://www.blogger.com/profile/17806011295213096009noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-52712002384625701062019-07-11T03:29:38.641-07:002019-07-11T03:29:38.641-07:00Hi Jim,
I am not getting any error, but in the Dat...Hi Jim,<br />I am not getting any error, but in the Data viewer i cant see any data just column names are being displayed.<br />Gouravhttps://www.blogger.com/profile/18258546587200253190noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-88636036468060686412019-04-26T06:21:50.544-07:002019-04-26T06:21:50.544-07:00I wonder if you have any thoughts around importing...I wonder if you have any thoughts around importing josn in sql 2014 when running from job I get this error using the json scrip component <br /><br />JSON Source - Videos failed validation and returned error code 0xC0048021. <br />The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Includes and runs custom script code. For example, apply a business rule that limits the range of valid values in an "income" column or add values in two columns and calculate the average of the sum.;Microsoft Corporation; Microsoft SQL Server; Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;10". <br />The component metadata for "JSON Source - , " could not be upgraded to the newer version of the component. The PerformUpgrade method failed.sishttps://www.blogger.com/profile/17082967645206516294noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-24622507367612994092019-03-05T09:31:01.769-08:002019-03-05T09:31:01.769-08:00Hi Jim im getting an error here
foreac...Hi Jim im getting an error here<br /> foreach (var metric in outPutMetrics)<br /> {<br /> Output0Buffer.AddRow();<br /> //Output0Buffer.Degreeurl = metric.Degree_url; <br /> }<br /><br />it say it can't convert string to an int<br /><br /><br />Anonymoushttps://www.blogger.com/profile/08225777673204506008noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-44964332184467298442019-03-05T08:37:32.916-08:002019-03-05T08:37:32.916-08:00Im getting an error on
Output0Buffer.lastname = m...Im getting an error on <br />Output0Buffer.lastname = metric.last_name;<br /><br />it says it cannot implicitly convert type string to intAnonymoushttps://www.blogger.com/profile/08225777673204506008noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-39318573405314036532019-01-08T01:53:41.983-08:002019-01-08T01:53:41.983-08:00Hey, I hope it is not to late to get help from you...Hey, I hope it is not to late to get help from you in 2019!(Happy New Year).<br />I am trying to get data from here: https://api.opendota.com/api/proMatches but it gives me <br />Error: 0x1 at Data Flow Task, Error Getting Data From Webservice!: System.NullReferenceException: The object reference has not been set to the instance of the object in ScriptMain.GetWebServiceResult(String wUrl).<br /><br />If you still on it I will share the code. Anonymoushttps://www.blogger.com/profile/13879207730706827893noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-87580714731545680002018-11-28T23:19:17.798-08:002018-11-28T23:19:17.798-08:00Hi Jim,
Some web APIs require a JSON/XML payload ...Hi Jim, <br />Some web APIs require a JSON/XML payload when we try to hit them. How should approach connecting to such APIs within SSIS?Anonymoushttps://www.blogger.com/profile/07352846170626192078noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-24421543106790848222018-11-22T02:03:15.717-08:002018-11-22T02:03:15.717-08:00Hi,
THANK YOU for this article! It's brillian...Hi, <br />THANK YOU for this article! It's brilliantly written. Anonymoushttps://www.blogger.com/profile/01362223929711776968noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-90845843956917927732018-10-18T06:50:22.741-07:002018-10-18T06:50:22.741-07:00Hi Jim, very nice article but I'm getting 2 er...Hi Jim, very nice article but I'm getting 2 errors... <br /><br />1- [Error Getting Data From Webservice!] Error: System.ArgumentException: Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property.<br />Parameter name: input<br /> at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize(JavaScriptSerializer serializer, String input, Type type, Int32 depthLimit)<br /> at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)<br /> at ScriptMain.GetWebServiceResult(String wUrl)<br /><br /><br />2-[Error Getting Data From Webservice!] Error: System.NullReferenceException: Object reference not set to an instance of an object.<br /> at ScriptMain.CreateNewOutputRows()<br /><br />any idea what to look for? <br />Anonymoushttps://www.blogger.com/profile/11002295961186057290noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-90865945978576333292018-10-02T21:00:09.306-07:002018-10-02T21:00:09.306-07:00This question is pretty ambiguous. This article de...This question is pretty ambiguous. This article details a Rest API call.Jimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.com