Executing SSIS Package in 64-bit server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Executing SSIS Package in 64-bit server

13 posts, Page 1 of 2

1 2 »»

Executing SSIS Package in 64-bit server

Rate Topic

Display Mode

Topic Options

Author

Message

CoThong

Posted Friday, December 18, 2009 1:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:01 PM
Points: 8, Visits: 49

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.

Post #836171

CozyRoc

Posted Saturday, December 19, 2009 8:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

Is there an error when you execute your package from code? If yes, what is the error?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #836803

CoThong

Posted Sunday, December 20, 2009 9:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:01 PM
Points: 8, Visits: 49

Hi CozyRoc,

Thanks for your reply.

I got the following error from the SSIS package log:

The AcquireConnection method call to the connection manager "QuickbooksExtract" failed with error code 0xC0202009.

where the QuickbooksExtract is an Excel Connection Manager.

I did some research through the internet and found that this happend because there's no 64-bit Jet provider which is used to deal with the excel files and I will need to run the package under 32-bit dtexec.exe in order to get this problem solved.

Any idea on how can I get this done?

Please advice.

Thanks.

Post #836964

CozyRoc

Posted Sunday, December 20, 2009 10:01 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

CorinaThong (12/20/2009)

I have another question for you. When you say you have web service, does the code you have implemented to execute the package run inside library (DLL) or it is part of an executable? Is it ASP.NET module?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #836966

CoThong

Posted Monday, December 21, 2009 8:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:01 PM
Points: 8, Visits: 49

Hi CozyRoc,

The package is running inside the library. Smile

Post #837701

CozyRoc

Posted Monday, December 21, 2009 8:29 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

CorinaThong (12/21/2009)

Hi CozyRoc,

The package is running inside the library. Smile

There are two options to execute your package in 32 bit mode:

- make the executable, which contains your library to execute in 32bit mode.
- execute the package using Process.Start method and selecting the 32bit DTEXEC.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #837709

CoThong

Posted Tuesday, December 22, 2009 12:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:01 PM
Points: 8, Visits: 49

I tried to use Process.start...but it doesn't execute the package at all ...not sure whether the code that I wrote is correct..

below is the code that I write for the Process.start:

Dim param As String
Dim arg As String
Dim packagePwd As String

Dim StartInfo As New ProcessStartInfo

packagePwd = "xxx"

param = "/set \package.Variables[A].Value;'\'" + a + _
"\'' /set \package.Variables[B].Value;'\'" + b + _
"\'' /set \package.Variables[C].Value;'\'" + c + _
"\'' /set \package.Variables[D].Value;'\'" + d + _
"\'' /set \package.Variables[E].Value;'\'" + e + _
"\'' /set \package.Variables[F].Value;'\'" + f + _
"\'' /set \package.Variables[G].Value;'\'" + g + _
"\'' /set \package.Variables[H].Value;'\'" + h + _
"\'' /set \package.Variables[I].Value;'\'" + i + _
"\'' /set \package.Variables[J].Value;'\'" + Convert.ToString(j) + _
"\'' /set \package.Variables[K].Value;'\'" + Convert.ToString(k) + "\''"

arg = "/f " + packagePath + " /de " + packagePwd + " " + param

StartInfo.FileName = "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"
StartInfo.Arguments = arg

Process.Start(StartInfo)

The part that I'm not really sure is the parameter part, I'm not sure whether I put the quotes correctly and well, I just follow the example that I found from the internet.

please do let me know if you found it wrong.

Thanks.

Post #837758

CozyRoc

Posted Tuesday, December 22, 2009 6:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

What is the error when you run your code? If your string contains backslash, you have to escape it like this:

StartInfo.FileName = "C:\\Program Files (x86)\\Microsoft SQL Server\\90\\DTS\\Binn\\dtexec.exe"

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #837870

CoThong

Posted Tuesday, December 22, 2009 8:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 25, 2011 1:01 PM
Points: 8, Visits: 49

There's no error but the package wasn't execute when the code runs, even after I have change the file name to escape the backslash.

What else can I do to the code? do you have any sample code to use the process.start?

Thanks.

Post #838350

CozyRoc

Posted Tuesday, December 22, 2009 9:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

CoThong (12/22/2009)

Did you review the sample code included in the link I have sent you? I would recommend you first start with very simple test and then gradually start testing with code getting closer and closer to your final result. That is how you will find where is the issue.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #838357

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions