July 28, 2015 at 3:43 am
If i run the below code through SQL managament studio.. it works. If I run it through SSIS a data flow task OLEDB Source.. i get the error attached
I have seen the SET RESULTS NONE posts but that didn't work for me..
Any ideas?
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
select
(select SERVERPROPERTY ('ServerName') ) as [ServerInstance],
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY ('Isclustered') as IsClustered,
(select value_in_use from sys.configurations
where configuration_id = '1544') AS [Max Server Memory],
(select value_in_use from sys.configurations
where configuration_id = '16390') as xp_cmdshell,
@portnumber as [Port]
July 28, 2015 at 8:30 am
SQLAssAS (7/28/2015)
If i run the below code through SQL managament studio.. it works. If I run it through SSIS a data flow task OLEDB Source.. i get the error attachedI have seen the SET RESULTS NONE posts but that didn't work for me..
Any ideas?
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
select
(select SERVERPROPERTY ('ServerName') ) as [ServerInstance],
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY ('Isclustered') as IsClustered,
(select value_in_use from sys.configurations
where configuration_id = '1544') AS [Max Server Memory],
(select value_in_use from sys.configurations
where configuration_id = '16390') as xp_cmdshell,
@portnumber as [Port]
Is it possible that you don't have permission to read that key from the registry, using the security context associated with the SSIS package ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 8:49 am
Hi, I have SA on every SQL instance and Local admin on each box so permissions won't be an issue.
July 28, 2015 at 9:01 am
SQLAssAS (7/28/2015)
Hi, I have SA on every SQL instance and Local admin on each box so permissions won't be an issue.
YOU have permissions, but that's not the question I asked. I asked if the security context provided by SSIS has those permissions. If an SSIS package is run as a SQL Agent Job, then the SQL Agent Service's security context will be used. Typically, you don't want the SQL Agent Service to have full admin rights.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 28, 2015 at 9:14 am
I have permission.. and I'm running the package via Visual studio trying to get it working, thats why I specified that I have permission.
July 28, 2015 at 9:31 am
SQLAssAS (7/28/2015)
I have permission.. and I'm running the package via Visual studio trying to get it working, thats why I specified that I have permission.
Okay... a quick Google search on this suggests you might try substituting a double-backslash for the single one just prior to "Microsoft SQL Server" in the following:
Instead of this:
@key = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
try this:
@key = 'Software\Microsoft\\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
Here's where I found it:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply