Importing custom file to server - T-sql

• Arek Garbacz

  SSC Rookie

  Points: 43

  More actions

  August 9, 2005 at 9:52 am

  #164549

  Hi

  I have created DTS Package which imports data from cvs file to sever.

  I would like to use it to import custom files

  so I used code to try to run in from T-SQL

  EXEC @HRESULT = sp_OACreate 'DTS.Package', @DTSPackageObject OUTPUT

  EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'LoadFromSQLServer("herkules", "user", "pass", 256, , , , "IMP_GTC_FIN")', NULL

  EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL

  and it works perfectly ....

  next step was creating Global Variable and placing it by "dynamic properties task" as "text file->data source" but the problem is when

  I start :

  EXEC @HRESULT = sp_OACreate 'DTS.Package', @DTSPackageObject OUTPUT

  EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'LoadFromSQLServer("herkules", "user", "pass", 256, , , , "IMP_GTC_FIN")', NULL

  EXEC @HRESULT = sp_OASetProperty @DTSPackageObject, 'GlobalVariables("GTC_FIN").Value' , 'd:\ag.csv'

  EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL

  IF @HRESULT <> 0

  BEGIN

  EXEC @HRESULT = sp_OAGetErrorInfo @DTSPackageObject,@ErrSource OUTPUT ,@ErrDescrip OUTPUT

  RAISERROR ( @ErrMsg,11,1)

  RETURN

  END

  i got message

  Server: Msg 50000, Level 11, State 1, Line 117

  Error running DTS package

  I have file ag.cvs on my computer and also on server, any ideas ?

  Thanks for help!!

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  August 9, 2005 at 12:01 pm

  #580829

  I didn't think you could have a RETURN in anything other than a stored-procedure??

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Arek Garbacz

  SSC Rookie

  Points: 43

  More actions

  August 10, 2005 at 2:58 am

  #580942

  Well yes, I just copy - paste from other source, but the thing is that when I'am at point

  EXEC @HRESULT = sp_OAMethod @DTSPackageObject, 'EXECute', NULL

  i got

  @HRESULT 0

  so the problem is "Error running DTS package" 😐 I'll try to google some more...

  Thanks

• mike gercevich

  SSCrazy

  Points: 2961

  More actions

  August 10, 2005 at 3:30 pm

  #581238

  Looks like a permission problem on the Package's current execution context.  Enable logging to a file that is local to the server, where the folder allows read/write/create access to everyone.  This is an easy way to fine out what local/domain account the Package is executing under.

  Since you are creating a file import package, I am guessing that the current owner of the DTS Package Object does not have permission to read from the specified folder you are passing in via the GlobalVariable.

  -Mike Gercevich