July 11, 2018 at 2:41 pm
Hello, I'm on a single SQL Server instance and box where both Dev and prod occur. I have VS 2015 installed, with Teradata Drivers and Attunity driver to reach the data source. All that is 32 bit for the VS/SSDT needs. The pkgs get deployed, and a job is created, steps added, etc and then its scheduled and runs. But it runs in 32 bit for the connection, as that's what is used to design the pkg.
I have also installed Teradata 64 bit driver and 64 Attunity on the same one server as well. Is, or would, the easiest way to switch my job that runs the steps, in the connection manager or the SSISDB? I look, both places look similar, with this is as a connection string: {Teradata};CHARSET=UTF8;LOGINTIMEOUT=20;DBCNAME=<sysnamehere>;UID=<usernamehere>;DATABASE=;ACCOUNT=;AUTHENTICATION=;WINAUTH=0. I'm not really seeing a good place to switch the jobs connection to use 64 drivers and providers. Thanks
July 11, 2018 at 3:23 pm
If you run the package through a sql server agent job, within a step defined as Integration Services Package there is a tick box on the advanced properties to elect it to run as 32 bit - by default it will run under the type of install - if SQL install is 64bit it will run as 64bit unless this box is ticked.
If running from the command line the same applies but on this case a parameter is supplied to force to 32bit if running on a 64bit server.
July 11, 2018 at 3:42 pm
frederico_fonseca - Wednesday, July 11, 2018 3:23 PMIf you run the package through a sql server agent job, within a step defined as Integration Services Package there is a tick box on the advanced properties to elect it to run as 32 bit - by default it will run under the type of install - if SQL install is 64bit it will run as 64bit unless this box is ticked.If running from the command line the same applies but on this case a parameter is supplied to force to 32bit if running on a 64bit server.
I already have it with that 32 bit switch turned off, if I turn t on, then it will error. How can i check to validate that when it is running that its at 64 bit then? when I run one of my jobs, and look at network traffic, its half that of others who are pulling in data at a known 64 bit. This has been a long term problem with someone before me not setting up the server quite right, but also a single box thats used both for Dev and Prod
July 11, 2018 at 4:00 pm
32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the dataflow tasks.
Exactly how are you running the packages? SQL Agent SSIS task or Command line dtexec? If your SQL Server is 64 bit and the flags are not set it will run in 64bit - if running through command line dtexec this can easily be verified by looking at the task manager for dtexec process - if it has a 32 in front of it it is running in 32bit mode else is running on 64bit.
July 11, 2018 at 4:41 pm
frederico_fonseca - Wednesday, July 11, 2018 4:00 PM32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the dataflow tasks.Exactly how are you running the packages? SQL Agent SSIS task or Command line dtexec? If your SQL Server is 64 bit and the flags are not set it will run in 64bit - if running through command line dtexec this can easily be verified by looking at the task manager for dtexec process - if it has a 32 in front of it it is running in 32bit mode else is running on 64bit.
It' being run via a built job and run by SQL Agent
July 12, 2018 at 5:48 am
quinn.jay - Wednesday, July 11, 2018 4:41 PMfrederico_fonseca - Wednesday, July 11, 2018 4:00 PM32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the dataflow tasks.Exactly how are you running the packages? SQL Agent SSIS task or Command line dtexec? If your SQL Server is 64 bit and the flags are not set it will run in 64bit - if running through command line dtexec this can easily be verified by looking at the task manager for dtexec process - if it has a 32 in front of it it is running in 32bit mode else is running on 64bit.
It' being run via a built job and run by SQL Agent
I've read this thread through a couple of times and I still don't really understand the issue. A SQL Agent job step which runs a package calls a program called DTExec.exe to run the package. DTExec.exe comes in 32- and 64-bit versions, and the one which is executed depends on how you have configured the step in SQL Agent. If you have installed only the 64-bit versions of the drivers on the server, you need to run the package in 64-bit mode.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 12, 2018 at 7:43 am
Phil Parkin - Thursday, July 12, 2018 5:48 AMquinn.jay - Wednesday, July 11, 2018 4:41 PMfrederico_fonseca - Wednesday, July 11, 2018 4:00 PM32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the dataflow tasks.Exactly how are you running the packages? SQL Agent SSIS task or Command line dtexec? If your SQL Server is 64 bit and the flags are not set it will run in 64bit - if running through command line dtexec this can easily be verified by looking at the task manager for dtexec process - if it has a 32 in front of it it is running in 32bit mode else is running on 64bit.
It' being run via a built job and run by SQL Agent
I've read this thread through a couple of times and I still don't really understand the issue. A SQL Agent job step which runs a package calls a program called DTExec.exe to run the package. DTExec.exe comes in 32- and 64-bit versions, and the one which is executed depends on how you have configured the step in SQL Agent. If you have installed only the 64-bit versions of the drivers on the server, you need to run the package in 64-bit mode.
The issue is that the OP thinks that the packages are not running in 64bit mode as the observed network speed while transferring data is lower than other packages he "knows" are running in 64bit mode.
So the OP wishes to validate that the packages are indeed running in 64bit mode.
July 12, 2018 at 12:20 pm
frederico_fonseca - Thursday, July 12, 2018 7:43 AMPhil Parkin - Thursday, July 12, 2018 5:48 AMquinn.jay - Wednesday, July 11, 2018 4:41 PMfrederico_fonseca - Wednesday, July 11, 2018 4:00 PM32/64 bit has nothing to do with network speed - if speed is lower the issue is with the package and what it is doing and the configuration of the dataflow tasks.Exactly how are you running the packages? SQL Agent SSIS task or Command line dtexec? If your SQL Server is 64 bit and the flags are not set it will run in 64bit - if running through command line dtexec this can easily be verified by looking at the task manager for dtexec process - if it has a 32 in front of it it is running in 32bit mode else is running on 64bit.
It' being run via a built job and run by SQL Agent
I've read this thread through a couple of times and I still don't really understand the issue. A SQL Agent job step which runs a package calls a program called DTExec.exe to run the package. DTExec.exe comes in 32- and 64-bit versions, and the one which is executed depends on how you have configured the step in SQL Agent. If you have installed only the 64-bit versions of the drivers on the server, you need to run the package in 64-bit mode.
The issue is that the OP thinks that the packages are not running in 64bit mode as the observed network speed while transferring data is lower than other packages he "knows" are running in 64bit mode.
So the OP wishes to validate that the packages are indeed running in 64bit mode.
Your right, and I look at the services of a job running right now, and it only shows dtexec.exe and no reference to it being 32 or 64 bit
July 12, 2018 at 12:54 pm
quinn.jay - Thursday, July 12, 2018 12:20 PMYour right, and I look at the services of a job running right now, and it only shows dtexec.exe and no reference to it being 32 or 64 bit
If you are looking at processes in Task Manager, do the following:
1) Switch to the Details pane
2) Right click one of the columns and 'Select Columns'
3) Display the 'Platform'
That will give you a definitive answer.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply