July 20, 2015 at 7:00 am
Hello SSC community,
I currently have several SSIS packages that operate the following way....
1) I pull data from a MYSQL DB (RSS Bus Salesforce)
2) Perform data transformations
3) Hit some lookup tables, and then push the data into our structure.
The issue that I am having is that Salesforce has a bad habit of dropping/adding columns without notification, which in turn causes my SSIS packages crash. I have heard that you can actually pull the table schemas from Salesforce database and compare it to SQL Schemas? This sounds like a great idea, but I wouldn't even know where to start, since this is the first time I have dealt with Salesforce (or MYSQL overall).
Does anyone have experience in this? I was considering maybe using a DDL trigger on my SQL Server table that populates a log table, which my SSIS packages check before the process runs. I read a chapter on DDL triggers a while back, and if memory serves this would be a good place for one.
Again, I am not sure how to accomplish this or if it's even possible.
Thank you all in advance and I look forward to your responses.
Thanks,
Dave
The are no problems, only solutions. --John Lennon
July 21, 2015 at 9:21 am
I am not sure what you mean in step 1 where you say you are pulling data from a MySQL DB and using the RSSBus Salesforce components. Are you pulling data from a MySQL cache of Salesforce data? Or are you using the RSSBus Salesforce components to pull data directly from Salesforce?
In both cases, you may be able to resolve your issue by simply using a SQL query in the source component to select only the columns you need in your data flow. Using the 'Table or View' access mode or a SQL 'SELECT *' query will both cause package validation errors if the metadata of the source changes. By issuing a query that explicitly selects only the fields you are interested in, you can avoid any issues (assuming those columns are not removed). For example, if you only wanted Name and Fax from the account table, you could use the query 'SELECT Name, Fax FROM Account'.
Please let us know if this does not resolve the issue for you.
July 21, 2015 at 11:41 am
I don't know much about MySQL, but back in the day when one of my SQL Server databases had similar schema issues, I set up a trigger to send me an email when the schema changed. And it did log to a table as well.
So if you have the ability to query MySQL for something like that INFORMATION_SCHEMA.Columns or Tables views, then setting something up for yourself would be a fantastic idea. You can't really do a dynamic connection manager in SSIS, but you could just use a Script Task or Execute T-SQL Task to do some dynamic T-SQL inserting based on the results of the schema response.
Does that help?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply