December 19, 2006 at 9:56 am
Hi,
Has anyone got any experience of running SSIS packages on a 64 bit server?
I'm struggling.....
I have developed the packages on my 32bit dev server and I'm trying to deploy them to a 64 bit server I have been given for the live environment.
I seem to be having problems with variables so far. For some reason the For Each statement doesn't work on the Variables collection in the 64bit environment.
For Each oVariable In Dts.Variables
sMessage += oVariable.Name + "=" + oVariable.Value.ToString + vbCrLf
Next
I had to change to this to make work.
For i = 0 To Dts.Variables.Count - 1
oVariable = Dts.Variables.Item(i)
sMessage += oVariable.Name + "=" + oVariable.Value.ToString + vbCrLf
Next
Fixed that one but now I'm stumped trying to pass a variable to SQL Task. I've
reduced by SQL statement to the following.
Declare @Region As Varchar(16)
Set @Region = ?
Which fails with the following error..
[Execute SQL Task] Error: Executing the query "Declare @Region As Varchar(16) Set @Region = ? " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This works fine on my 32bit dev server.
Does anyone have any advice on 64bit SQL and SSIS?
Regards
Daniel
Some server details if it helps.
@@Version
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
WinMSD
OS NameMicrosoft(R) Windows(R) Server 2003 Enterprise x64 Edition
Version5.2.3790 Service Pack 1 Build 3790
Other OS Description R2
OS ManufacturerMicrosoft Corporation
System NameEDENTDW01
System ManufacturerVMware, Inc.
System ModelVMware Virtual Platform
System Typex64-based PC
ProcessorAMD64 Family 15 Model 65 Stepping 2 AuthenticAMD ~2201 Mhz
ProcessorAMD64 Family 15 Model 65 Stepping 2 AuthenticAMD ~2201 Mhz
December 20, 2006 at 2:26 am
Hi,
I found that SP1 for SQL Server hadn't been installed and as if by magic the problems disappeared once it was!
Regards
Daniel
December 20, 2006 at 5:40 am
If you haven't already noticed, you cannot DEBUG script on 64-bit. It doesn't stop at break-points. The only lame way I found to debug is to add the old MSGBOX popups with your variables,...
I've also had problems with 64-bit packages failing when scheduled as a job. There seems to be different permissions issues because of 64-bit. It seems to work o.k. if you use OLEDB Destination, instead of SQL Server destination.
steve
December 20, 2006 at 6:15 am
Hi Steve,
I'm tending to use the Msgbox option already to show variables values as you can see from my example above and I have already updated my connections to OLEDB from SQL Server destination because I found they didn't work as I expected. Thisis my first project with SSIS after DTS and I'm quite frustrated with the number of things that don't work!
I'm not sure if this helps you with you problem? I found it looking for something else.
http://support.microsoft.com/kb/918760
Cheers
Daniel
December 21, 2006 at 9:47 pm
Steve,
I have done quite a bit of deployment to Intel chipset Win64 environments and found a couple of odd things related to VBScript component usage:
1. You must compile them to binary for final deployment (SQL 2005 Agent SSIS job step or DTEXEC OS command). This can be done in the Win32 environment, but see #2 below.
2. After deploying from Win32 to Win64 I had to :
a. open up each SSIS package which had a VBScript component
b. open each VBScript component | Save it | Save the SSIS package in order to avoid getting bizarre corruption error messages at runtime. (...and, no, I'm not on drugs)
3. Running the script from a SQL Server 2005 Agent job step means the script's credentials will be those of either the SQL Server Agent or the proxy role you establish in SQL Server 2005 and set for the Run As setting of the job step. If your SSIS package uses NT Authentication to connect to remote flat files or databases, the appropriate credentials (SQL 2005 Agent or Proxy) must have the necessary permissions on the remote server.
As someone said earlier in this post, you MUST make sure you have SP1 and all the hotfixes applied for the X64 chipset...there are some semi-nasty SSIS bugs that were addressed by the hotfixes which can cause looping issues even on a 32-bit platform.
Good luck!
Best regards,
Don McMunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply