July 1, 2022 at 12:35 pm
OK so I have a customer with a hosted website with eCommerce options. Essentially if you a good CC and the shipping address matches the address on the card, they will take your order.
This order information is stored in a MySQL database that they log into via a website, where they proceed to physically print these orders and and re-key the info into their ERP system manually. (ugly I know). This ERP system is the MS SQL 2012 I mentioned in the title.
So we already have a very nice API for the ERP system, but it only reads other MS SQL tables. So I want to use SQL Server Integration Services (SSIS) to periodically get the MySQL data and dump it into a MS SQL table. Then the API can take it from there and eliminate the manual entry.
My issue is I have never done anything like this from an external data source and a non MS SQL data source at that.
Is this doable? Has anyone else out there handled anything similar to this? I thought I would just jump in and start to try this myself but then I thought I'd ask here first. Any insights appreciated.
July 1, 2022 at 3:21 pm
It sounds very doable and the way you approach it depends on the volume of data.
We extract a lot of data from Oracle using the Attunity drivers, because the built in drivers are too slow. Here's an article on connecting to MySQL.
Assuming you don't want to extract the entire data set every time, you need to come up with a way to identify new or changed data. If there is a reliable datetime column then you can extract only data that has changed since the last time you extracted. In the past I have done this, but it was a last edit time and the source system liked to backdate the time, so I extended my time window back an hour from the last time I ran the extract. It's better to extract too much data (within reason) and filter it later, than risk missing changes.
If there is no way to identify new data or changes are not an issue, but there is a unique identifier of some kind, you can use a lookup in a dataflow with a conditional split. Direct the "no match" output towards your import table, and direct the "match output" to a row count variable. This allows you to import new data and count old data to audit the totals, if applicable.
Once you have your data in an import table you can look for updates and changes and transform the data to the types you need for the API.
July 4, 2022 at 1:39 pm
This was removed by the editor as SPAM
July 4, 2022 at 3:27 pm
I got this,...
What did you end up doing? I know this would help others in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply