Executing SSIS Package in 64-bit server
-
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 12 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply