Executing SSIS Package in 64-bit server
-
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.
-
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/
-
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.
-
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/ -
Hi CozyRoc,
The package is running inside the library. 🙂
-
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/ -
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.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.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.
-
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/ -
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.
-
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/ -
Hi CozyRoc,
The sample code that you mean is it in the Process.Start link? I will try to test it out again, hope to get the solution.
Thanks.
-
We experienced simmilar issue:
Packages designed in 32bit, then migrated to 64bit does not work.
Solution:
1. Open dtsx package on VS on 64Bit
2. open up all "Script Tasks" into "Design Scripts"
3. Save and Close "Design Scripts"
4. Save and Close "Script Tasks"
5 repeat for all Script Tasks"
6. Save DTSX Package, when prompted to over write, say Yes.
This basically re-compiles the package for 64Bit Mode.
Remeber to delete the Package and import the new re-compiled package for the relevant jobs
| If in Doubt...don't do it!! |
-
Hi thlubbe,
Thanks for the guide. Anyway, I managed to solve the problem by running the package using the Package.Start. It runs failed due to the incorrect form of the argument that I did previously. I adjust the argument definition anf the following code works well:
Dim param As String
Dim arg As String
Dim packagePwd As String
Dim process_output As Process
Dim StartInfo As New ProcessStartInfo
packagePwd = "xxx"
param = "/set \package.Variables[A].Value;""" + a + """" + _
" /set \package.Variables.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.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_output = Process.Start(StartInfo)
Thanks everyone for the helpful information.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply