Changing DTSX SMTP Server Connection.

• Fëanor

  Old Hand

  Points: 326

  More actions

  June 11, 2009 at 12:10 pm

  #384759

  Hello world,

  Today DomainAdmin telling me that I must to change SMTP Server connection for every MSSQL on production environment :w00t: and I don't take in count all DTSX that are load in my servers. Regarding DTS the specific problem is that a lot of them are having a SMTP Connection to delivery mail alerts.

  Anyone know how i can change this specific set and avoid the GUI of SSIS? DTExec command line?

  Thanks in advance.

• Fëanor

  Old Hand

  Points: 326

  More actions

  June 12, 2009 at 12:14 pm

  #1008840

  I Can´t solve it yet. But I am trying with UPDATETEXT T-SQL sentence now.

  Involved Tables:

  msdb..sysdtspackages90 (package column has DTS definitions)

  TestDB..TestTB (I generated this table with a SELECT INTO on sysdtspackages90, for testing purposes)

  BEGIN TRAN

  DECLARE @ptrval VARBINARY(16)

  DECLARE @patval INT

  SELECT @PATVAL = PATINDEX ( '%SMTPSERVER=10.1.10.6%' , CAST(CAST(PACKAGEDATA AS VARBINARY(MAX)) AS VARCHAR(MAX))) FROM TestDB..TestTB

  SELECT @ptrval = TEXTPTR(packagedata) from TestDB..TestTB where [name] = 'Test01'

  UPDATETEXT TestDB..TestTB.packagedata @ptrval @patval 9 '10.1.10.19'

  COMMIT

  @ptrval = pointer image field,

  @patval = start position,

  9 = delete_length

  After firsts test SMTP IP change to some hexadecimal value(*)...¿? and if you export to sysdtspackages90 table, DTS breaks...

  * = '{06A42AF5-A3B7-4C93-A0F8-5602C627C6D5}'