Update Item weights form GP to custom DB
Manny Lopez asked 6 years ago
Hello,
I thought this was going to be an easy one…but having issues. I’ve been asked create a nightly job that updates items weights in GP to a custom database. So I created a bulk data load and chose the data source as an ODBC connection. The source query is simple (select ITEMNMBR, ITEMSHWT from IV00101). For my destination, the only option I have is MS SQL Table. So I chose the destination table, it already contains data.. I just need to update the weights for the items (no need to create records). Also, the destination table has a required filed (index) that I cannot map from my source… How do I get this integration working? I’m I going about it correctly or is there a different way?
Thanks!
I thought this was going to be an easy one…but having issues. I’ve been asked create a nightly job that updates items weights in GP to a custom database. So I created a bulk data load and chose the data source as an ODBC connection. The source query is simple (select ITEMNMBR, ITEMSHWT from IV00101). For my destination, the only option I have is MS SQL Table. So I chose the destination table, it already contains data.. I just need to update the weights for the items (no need to create records). Also, the destination table has a required filed (index) that I cannot map from my source… How do I get this integration working? I’m I going about it correctly or is there a different way?
Thanks!
Answers
Patrick Roth Staff answered 6 years ago
When going to a SQL table, SmartConnect finds the PK of the table and uses that value as the “where” clause automatically. Meaning that you would always have to have it available and mapped.
Two ways to solve it:
1. Easiest is to just make a stored procedure taking the Item & ship Weight fields as in params and the two required “out” params.
https://www.eonesolutions.com/help-article/kba-01194-y4x3s3/
Now in your stored procedure, you can run the update statement however you want.
2. use a multi data source map.
the query to GP is one source
and the 2nd is to the destination table.
then join them together by the Item Number and return 3 fields. the key field, the item, and the ship weight.
now you can map all 3 fields in the table destination and SC should be update to make the appropriate “where” clause since we are providing the key field(s)
Two ways to solve it:
1. Easiest is to just make a stored procedure taking the Item & ship Weight fields as in params and the two required “out” params.
https://www.eonesolutions.com/help-article/kba-01194-y4x3s3/
Now in your stored procedure, you can run the update statement however you want.
2. use a multi data source map.
the query to GP is one source
and the 2nd is to the destination table.
then join them together by the Item Number and return 3 fields. the key field, the item, and the ship weight.
now you can map all 3 fields in the table destination and SC should be update to make the appropriate “where” clause since we are providing the key field(s)
Manny replied 6 years ago Hi Patrick,
Option 2 worked, Thanks!