Executing SSIS Package in 64-bit server

• CoThong

  Mr or Mrs. 500

  Points: 524

  More actions

  December 17, 2009 at 7:19 pm

  #103867

  Hi,

  I got the mentioned problem since a few weeks ago when I try to deploy the SSIS package into an SQL server of 64-bit. After a few weeks googling, I still couldn't find out the right way to get it solve, so I decided to post the question here and hope to get the solution from here.

  The system looks like this:

  Let’s say Server A represents Web Server and Server B represents the Sql server.

  Web Site (deployed in Server A) -> Web Services (deployed in Server B) -> SSIS packages (deployed in Server B) -> Database (in Server B).

  Web Site: User upload an excel file, which will be processed in the SSIS package later, through this web site. The details of the upload will then be passed to the web services for further action.

  Web services: Call the SSIS package here along with the package variables.

  SSIS package: Process the uploaded excel file and transform into another excel file.

  Database: Refer to the data in database during SSIS package execution.

  In Web services, I’m using the following way to call the SSIS package:

  Imports Microsoft.SqlServer.Dts.Runtime

  Dim package As Package

  Dim app As New Application

  Dim result As DTSExecResult

  app.PackagePassword = "xxx"

  Package = app.LoadPackage(packagePath, Nothing)

  Package.Variables("A").Value = a

  Package.Variables("B").Value = b

  Package.Variables("C").Value = c

  Package.Variables("D").Value = d

  Package.Variables("E").Value = e

  Package.Variables("F").Value = f

  Package.Variables("G").Value = g

  Package.Variables("H").Value = h

  Package.Variables("I").Value = i

  Package.Variables("J").Value = j

  Package.Variables("K").Value = k

  Package.Variables("L").Value = l

  result = Package.Execute

  I call it in this way as I will need the execution result, either success or fail, to pass back to the website as a status reporting.

  I understand that I will need to call dtexec.exe from Program Files (x86) in order to run the SSIS package in 64-bit server, but may I know how can I do it with the above way?

  Is the Microsfot.SqlServer.Dts.Runtime only applicable to 32-bit server?

  If the above way cannot be used, what’s the alternative way should I use to get the same result?

  Please advice.

  Thanks.