SQL Server Bulk Insert
I am attempting to do my first connection to SQL server and bulk insert data from pages of records out of a source system. I have successfully connected and it is inserting the first record out of 20 in my test. What do I need to change to get it to insert all 20 records?
I have an export from the source system with data that looks like this:
An import mapping that looks like this:
And an import to SQL that looks like this:
0
Comments
Michael Gantt on your export, add “data” to the “path to records in HTTP response body” under the “non-standard api response patterns” section. You'll then have to update your import mappings to get rid of the data field.
We have a current bug on mapper 2.0 for database and data warehouse imports where if the top mapping is to an array, only the first object gets loaded. It is being fixed for some databases in next week's release and then SQL Server and other data warehouses will be fixed in Nov release. In your case though, you can easily work around it by setting the path to records on your export.
Tyler Lamparter - Thank you for the suggestion. That worked to get all 20 records into SQL for this example. The next problem is that the export requires pagination (something I hadn't gotten working quite yet). The response from the source system provides a next_page relative URI in metadata.next_page as shown below. If I change “path to record in HTTP response body” to “data”, I no longer see the metadata.next_page values in the output.
Also, the handlebar expression I have below wasn't working either, even before this change, but I hadn't gotten to troubleshooting that yet.
Is there a way to accomodate the pagination and fix the original issue with writing multiple records to SQL?
Michael Gantt since your record is now the data path, the next_page url isn't there. So your handlebar path would now be:
{{previous_page.full_response.metadata.next_page}}Also Michael Gantt , which app is this? We can consider adding a connector for it to the platform.
Tyler Lamparter - Thank you! That is exactly what I needed. I had to add another {} around what you sent to get the decoded version, but that worked.
Matt Graney - The app I am exporting from is SafetyCulture. API documentation here: https://developer.safetyculture.com/reference/introduction
I think this is the final issue I'm having… I'm having trouble figuring out how to tell the flow that the last page has been received from the source application.
The last response looks like this:
I tried setting the paging settings like this (both blank as shown and with the word null typed in, with the same result):
I always get this error:
Michael Gantt maybe try
{{#if previous_page.full_response.metadata.next_page}}{{{previous_page.full_response.metadata.next_page}}}{{/if}}for the “override relative uri for subsequent page requests” and leave everything else blank?If that doesn't work, maybe try adding
metadata.next_pageto the "path to paging complete field in http response body and then for the value field below it put 0.I’m still getting the same error with both of those options.
It looks like the if handlebar statement is the one that makes the most sense. However, with that statement, it is still sending one final request to the source system with a null relative URI:
Michael Gantt thanks for the invite to your account to troubleshoot. The only other thing I needed to add was the “Override HTTP status code for last page”. Normally, an api would return a 404 for the last page, but in this case the api always returns a 200. So having the combination of that with the other fields seems to do the trick.
To troubleshoot, I just enabled debug logs on the export, changed some configurations, clicked preview, check logs for what calls were made, changed some configurations, and then found the combination that did the trick.
That makes perfect sense. Thanks so much for the help and quick response!
Please sign in to leave a comment.