Executing SqlDataCompare from anExecute Process task in SSIS
Al C
Posts: 12
I have an Sql Server Integration Services (SSIS) package with an Execute Process task that exec's SqlDataCompare.exe. I specify the arguments in xml and use the /argfile:"C:\Argfile.xml" switch. In the xml I specify 1 table name and the <sync/> option. I have 2 issues:
1. If SqlDataCompare determines that the tables are identical then it the exit code is 63 and the task fails. I want the task to succeed if the exit code is either 0 or 63. Unfortunately, SSIS only allows me to specify a single "Success" value and I have it set to 0. I would like it if there were a way that I could tell SqlDataCompare to return 0 (success) instead of 63 if the tables are identical.
2. I'm specifying the connection string in the xml using <s1>, <db1>, <u1>, <p1> etc. However, I would prefer to be able to pick up the connections from the SSIS's Connection Manager somehow. So I would tell it that the source connection is "Connection1" and the Destination connection is "Connection2" where COnnection1 and 2 are names of Connection Manager objects that I have defined in the SSIS package. I've tried writing code to dynamically create the command-line arguments for the connection using the properties of the connection manager, but I wasn't able to get at the password because SSIS won't expose it since it is sensitive data.
Any ideas/workarounds?
1. If SqlDataCompare determines that the tables are identical then it the exit code is 63 and the task fails. I want the task to succeed if the exit code is either 0 or 63. Unfortunately, SSIS only allows me to specify a single "Success" value and I have it set to 0. I would like it if there were a way that I could tell SqlDataCompare to return 0 (success) instead of 63 if the tables are identical.
2. I'm specifying the connection string in the xml using <s1>, <db1>, <u1>, <p1> etc. However, I would prefer to be able to pick up the connections from the SSIS's Connection Manager somehow. So I would tell it that the source connection is "Connection1" and the Destination connection is "Connection2" where COnnection1 and 2 are names of Connection Manager objects that I have defined in the SSIS package. I've tried writing code to dynamically create the command-line arguments for the connection using the properties of the connection manager, but I wasn't able to get at the password because SSIS won't expose it since it is sensitive data.
Any ideas/workarounds?
Thanks, Al
Comments
Unfortunately the return codes are hard-coded in SQL Data Compare and you can't configure them. Using the SQL Toolkit API, you could build your own application that will return 0 if the data is identical.
Likewise, I'm not savvy enough to know how to pull passwords out of Connection Manager if it's at all possible. Maybe you could hard-code them in. At the worst, you could just use Windows Authentication.
Sorry I can't be more helpful.
I don't know if you could use a batch file to go wrap up the program. Similar to this
Hope that helps
David