August 18, 2020 at 4:23 pm
a) You said that you can execute (not deploy) the package from SSDT, correct? I said that I can execute the package as well as deploy it from my local workstation SSDT.
b) If yes, try executing from SSDT after changing the Run64BitRuntime setting. Run64BitRuntime only applies within SSDT. It does not affect deployed packages.
From my local SSDT, my package always runs successfully with no errors, irrespective of Run64BitRuntime - FALSE or TRUE. Does not make difference.
Oracle connectivity problem of this package comes into picture when I run same package using DTEXec command from my workstation (main reason for this thread) .
This package is discussion, when executed from my local workstation SSDT as well SSIS DB catalog server, connects to oracle successfully without any issues. but fails to connect oracle only when executed using DTEXEC command from my workstation.
August 18, 2020 at 4:50 pm
what you are failing to understand is that your command line is executing the package ON THE SERVER and as such all resources required NEED to be on the server - the command line you use is only kicking off the execution.
so install oracle client on the server as I stated and it should work
if you wish to execute the package locally on the command line you can do it also - different command line, and you point it to the .ispac file instead of the server
August 18, 2020 at 5:08 pm
Hi Fredrico
Thanks for your time to respond to my post. Oracle client is already installed on Server.
Question, When i run DTEXEC command from my workstation, which oracle client is used by dtsx package while executing ? Oracle client installed on SSID DB catalog server (where packages are executed) OR client installed on my local workstation ?
As per my knowledge, in case of SQL Server Agent job configured on SSISDB catalog server, it simple uses all server resources and works perfectly. but this DTEXEC is confusing me a lot...
August 18, 2020 at 5:30 pm
it depends.
dtexec has a varied set of options.
options 1 and 2 use local resources - option 3 uses server resources.
there are 2 other options /SQL and /DTS - do not even look at them and do not use them in any circumstance as they are way obsolete
when using option 3 DTEXEC is just executing a set of stored procs on the SSISDB catalog database - you could easily execute them yourself (as many people do) and accomplish the same results.
if using SQL Server Agent (and assuming SSIS execution - not a command line ) it will by default use the 64 bit version of it unless the"use 32bit runtime" option is set.
if using dtexec to execute locally you either use a 32 bit of dtexec or a 64bit of dtexec with option "/86"
kicking off a Project based package using TSQL the default is to use the 64bit engine - again can be overridden by setting some parameters when creating the execution instance.
in your workstation the only way to get the 64bit of DTEXEC is to have a SQL Server installation done - there is no
some useful info at https://www.sqlshack.com/an-overview-of-dtexec-utility-in-ssis/
August 20, 2020 at 4:08 pm
If the Package runs on the SSIS server successfully and not on you local machine most likely the problem is your Oracle Client isn't configured. you can copy the TNSnames file from the ssis server but there is still a good bit of configuration needed in the oracle client to make a connection. take a look below
August 20, 2020 at 4:14 pm
Thanks Wayne for this reply. I will check this and let you know...
August 20, 2020 at 4:16 pm
If the Package runs on the SSIS server successfully and not on you local machine most likely the problem is your Oracle Client isn't configured. you can copy the TNSnames file from the ssis server but there is still a good bit of configuration needed in the oracle client to make a connection. take a look below
Nice idea, but as it successfully runs locally in SSDT, I am not sure that this will solve it.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 20, 2020 at 4:23 pm
That error almost always relates to mis-configured Oracle client connection in the package. either the Oracle client isn't setup correctly or the connection string the package calls is different between the 2 machines
August 24, 2020 at 3:53 pm
Hello Everyone
Thanks for showing your interest to help me for this problem. Even though I could not resolve this problem, I got workaround for this issue.
Instead of calling DTEXec command, I am calling database stored procedure "ssisdb.catalog.start_execution" from my powershell code script.
With this script, my SSIS package is running fine on SQL SSIS DB server and i am able to establish oracle connectivity.
Only problem with this approach is i must run this code using Windows Authentication. But in real world scenario, it will be my Mainframe ESP agent (machine_name$ id) that will be calling this script.
Can a non-Windows ID (ID which is not a Active Directory ID but, a generic ID) execute "ssisdb.[catalog].[create_execution]" ? If so what steps are required ?
Please advise. Below is the code I used.
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand ("ssisdb.[catalog].[create_execution]", $sqlConn)
$sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
$sqlCmd.Parameters.AddWithValue("folder_name", $TargetFolderName) | Out-Null
$sqlCmd.Parameters.AddWithValue("project_name", $ProjectName) | Out-Null
$sqlCmd.Parameters.AddWithValue("package_name", $PackageName) | Out-Null
$sqlCmd.Parameters.Add("use32bitruntime", [System.Data.SqlDbType]::Bit).Value = $RunIn32Bit.IsPresent
$sqlCmd.Parameters.Add("execution_id", [System.Data.SqlDbType]::BigInt).Direction = [System.Data.ParameterDirection]::Output
$sqlCmd.ExecuteNonQuery() | Out-Null
[int64]$execID = $sqlCmd.Parameters["execution_id"].Value
$sqlCmd.Dispose()
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply