How to Create SQL Scripts in Integration Manager without hardcoding the database

Here is a handy bit of code. Have you ever wanted to run a sql script in Integration manager after the task completes but you don't want to hard code the company database so you can use it against multiple companies?

Well here is the code to do this (in version 10), in the script window within Integration manager start off by setting the ADODB connection.

set MyCon = CreateObject("ADODB.Connection")

Then use the connection string to bring back the inter company ID

MyCon.Connectionstring = "database=" + GPConnection.GPConnInterCompanyID

Open your connection

GPConnection.Open(MyCon)


You can then construct your SQL statement, the below is an update statement

updatecommand = "update PM00200 "
updatecommand = updatecommand & " set PHNUMBR1 = 'xxxxx',"
updatecommand = updatecommand & " PHNUMBR2 = 'xxxxx',"
updatecommand = updatecommand & " PHONE3 = 'xxxx',"
updatecommand = updatecommand & " FAXNUMBR = 'xxxx'"

Then close the record set and exercute the command

recset = MyCon.Execute(updatecommand )

This becomes a very powerful tool when you combine it with before scripts which will enable you to fill out blank fields with place holders etc and then remove the place holders after the integration completes.