Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Executing SSIS Package in 64-bit server Expand / Collapse
Author
Message
Posted Friday, December 18, 2009 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Saturday, December 19, 2009 8:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
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
Posted Sunday, December 20, 2009 9:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, December 20, 2009 10:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
CorinaThong (12/20/2009)
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.


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
Posted Monday, December 21, 2009 8:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #837701
Posted Monday, December 21, 2009 8:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
CorinaThong (12/21/2009)
Hi CozyRoc,

The package is running inside the library.


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
Posted Tuesday, December 22, 2009 12:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, December 22, 2009 6:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
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
Posted Tuesday, December 22, 2009 8:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, December 22, 2009 9:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 5:27 PM
Points: 1,117, Visits: 2,219
CoThong (12/22/2009)
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.


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 »

Add to briefcase 12»»

Permissions Expand / Collapse