Calling a SISS package from MS Access

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  November 17, 2006 at 6:37 am

  #115089

  Hi,

  I recently implemented a call from MS Access to call a dts package but have now been told that the production server is SQL 2005.

  I have created a new SISS project which is stored on a local server.

  Question is how do I amend my call to the new SISS package.

  The new SISS package has one task which is a execute DTS task.

  My call from Access to call the original DTS package was

  '------------------------Start of VBA call

  'run DTS package

  Dim oPkg, oStep, sMessage, bStatus

  Const DTSSQLStgFlag_Default = 0

  Const DTSStepExecResult_Failure = 1

  Set oPkg = CreateObject("DTS.Package")

  oPkg.LoadFromSQLServer "MyServer", "MyUserName", "MyPwd", 0, , , , "DTS Package Name"

  oPkg.Execute

  Set oPkg = Nothing

  '------------------------End of VBA call

  I don't know if SISS packages are stored externally but that is what it looks like to me.

  Any help VERY welcome.

  Eamon

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  November 17, 2006 at 7:52 am

  #672600

  sorted ...

  I just changed this line

  oPkg.LoadFromSQLServer "MyServer", "MyUserName", "MyPwd", 0, , , , "Full path name and name of DTS package.dtsx"