项目外部网络

项目外部网络

关注
发信
关注(28)粉丝(399)

部署SSIS（SQL Server 2012）项目外部网络

本文介绍了部署SSIS（SQL Server 2012）项目外部网络的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我已经在我的电脑上构建了一个SSIS项目，并且在执行程序包的同时，通过SQL认证连接到SQL Server Server之外。现在是将项目部署到同一个服务器的时候了，但是我已经打了墙。在进行内置部署向导时，输入服务器名称（正如我已经输入连接到连接管理器的数据库一样）并获得不可信域错误（需要Window的身份验证而不是SQL身份验证）。



我认为使用参数在项目中设置环境特定值也可能是相关的。



如何将SSIS项目部署到网络之外的SQL Server？



我还应该补充说在我的搜索中，我遇到了一个正在努力使用VPN连接（没有发布分辨率）的人，这是怎么通常完成的？

解决方案

您有3个工具用于将.ispac文件部署到SSISDB目录中。


  1. ISDeploymentWizard.exe


  3. TSQL

您已经在使用向导，因为没有工作认证问题。我认为即使你的MOM方法，你仍然会遇到auth问题。这样就可以使用TSQL方法，因为你有一个有效的登录，希望这将工作。



以下代码将序列化您的.ispac，创建部署文件夹（如果尚不存在），部署项目然后分配参数值。



在SSMS中，您需要将模式更改为查询菜单下可用的SQLCMD模式。这样做之后，按Ctrl-Shift-M打开宏事件，它将允许您指定可以找到.ispac文件的位置。 

 使用SSISDB 
 GO 
 
 IF（'$（isPacPath）'='$ '+'（isPacPath）'）
 BEGIN 
 THROW 50000，N'This脚本必须以SQLCMD模式运行。 
 END 
 GO 
 
  - 您必须在SQLCMD模式
  -  setvar isPacPathC：\sandbox\SSDTDeploy\TSQLDeploy\bin\\ \\Development\TSQLDeploy.ispac
：setvar isPacPath< isPacFilePath，nvarchar（4000），C：\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac> 
 
 
 
 DECLARE 
 @folder_name nvarchar（128）='TSQLDeploy'
，@folder_id bigint = NULL 
  - 必须匹配ispac 
，@project_name nvarchar（128）='TSQLDeploy'
，@project_stream varbinary（max）
，@operation_id bigint = NULL; 
 
  - 从源文件中读取zip（ispac）数据
 SELECT 
 @project_stream = T.stream 
 FROM 
（
 SELECT 
 * 
 FROM 
 OPENROWSET（BULK N'$（isPacPath）'，SINGLE_BLOB）AS B 
）AS T（stream）; 
 
  - 测试目录存在
如果不存在
（
 SELECT 
 CF.name 
 FROM 
 catalog.folders AS CF 
 WHERE 
 CF.name = @folder_name 
）
 BEGIN 
  - 为我们的项目创建文件夹
 EXECUTE [catalog]。[create_folder ] 
 @folder_name 
，@folder_id OUTPUT; 
 END 
 
  - 实际部署项目
 EXECUTE [catalog]。[deploy_project] 
 @folder_name 
，@project_name 
，@ project_stream 
，@operation_id OUTPUT; 
 
  - 检查是否有事情发生错误
 SELECT 
 OM。* 
 FROM 
 catalog.operation_messages AS OM; 
 
  - 使用此设置参数
  -  http://msdn.microsoft.com/en-us/library/ff878162.aspx 
 EXECUTE catalog.set_object_parameter_value 
  - 使用值20表示项目参数
  - 或值30表示包参数
 @object_type = 20 
，@folder_name = @folder_name 
 ，@project_name = @project_name 
，@parameter_name = N -  nvarchar（128）
，@parameter_value = NULL  -  sql_variant 
，@object_name = N -  nvarchar 260）
，@value_type ='' -  char（1）
  - 使用字符V表示parameter_value是一个字面值
  - 默认情况下将使用其他值在
之前分配给执行。 
  - 使用字符R表示parameter_value是引用值
  - 并已设置为环境变量的名称。 
  - 此参数是可选的，默认情况下使用字符V

如果您参加SQL Pass Summit 2012大会上，我在关于，但我没有覆盖部分。我相信最后一个电话是正确的，但我还没有验证。我已经链接到过程调用的文档，以便您可以根据您的具体需求进行调整。



示例



2013年8月更新



我在当前的客户端学到了一些东西。我们有我们的笔记本电脑加入了家庭领域。我们在客户的AD网络上有帐户。每当我需要在客户的世界中做某事时，我需要启动我的过程并指示它呈现我的外国凭据。什么使这成为可能的是。替代



我创建了一个套件的批处理文件，启动我需要的每个进程。他们采取

  runas / netonly：ForeignDomain\DoppelgangerC：\windows\system32\cmd的形式。我有一个用于命令提示符（上图），Visual Studio，SSMS，PowerShell，PowerShell（$），



ISE和其他一些特殊应用程序需要使用他们的域。



使用runas方法，我已经能够使用上述所有方法部署软件包（如以及直接从使用外部凭据运行的Visual Studio实例部署）。


I have built an SSIS project on my PC, and am connecting to an SQL 2012 Server outside our network (via SQL Authentication) while executing the package. It is now time to deploy the project to that same server, but I have hit a wall. While going through the built-in deployment wizard, I enter the Server Name (just as I have entered it to connect to the database withing the connection managers) and get an "untrusted domain" error (it is requiring Window’s Authentication rather than SQL Authentication).

I think it may also be relevant that I am using parameters to set environment-specific values in the project.

How do I deploy an SSIS project to a SQL Server outside the network?

I should also add that in my searches, I did come across someone who was struggling to do this with a VPN connection (with no resolution posted), is that how this is typically done?

解决方案

You've got 3 tools for deploying .ispac files into the SSISDB catalog.

  1. ISDeploymentWizard.exe
  2. ManagedObjectModel
  3. TSQL

You are already using the wizard and it's not working due to auth issues. I think even with your MOM approach, you'll still run into auth issues. That leaves the TSQL approach and since you have a valid logon, hopefully this will work.

The following code will serialize your .ispac, create the deployment folder if it does not already exist, deploy the project and then assign a parameter value.

In SSMS, you will need to change your mode to SQLCMD mode which is available under the Query menu. After doing that, hit Ctrl-Shift-M to bring up the macro-thing and it will allow you to specify where the .ispac file can be found. 

USE SSISDB
GO

IF ('$(isPacPath)' = '$' + '(isPacPath)')
BEGIN
    THROW 50000, N'This script must be run in SQLCMD mode.', 1;
END
GO

-- You must be in SQLCMD mode
-- setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac>"



DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
    -- this must match the ispac
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT
        *
    FROM
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder]
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project]
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

-- Check to see if something went awry
SELECT
    OM.*
FROM
    catalog.operation_messages AS OM;

-- Use this to set parameters
-- http://msdn.microsoft.com/en-us/library/ff878162.aspx
EXECUTE catalog.set_object_parameter_value
    -- Use the value 20 to indicate a project parameter
    -- or the value 30 to indicate a package parameter
    @object_type = 20
,   @folder_name = @folder_name
,   @project_name = @project_name
,   @parameter_name = N'' -- nvarchar(128)
,   @parameter_value = NULL -- sql_variant
,   @object_name = N'' -- nvarchar(260)
,   @value_type = '' -- char(1)
--  Use the character V to indicate that parameter_value is a literal value
-- that will be used by default if no other values are assigned prior
-- to execution.
-- Use the character R to indicate that parameter_value is a referenced value
-- and has been set to the name of an environment variable.
-- This argument is optional, the character V is used by default

If you attended the SQL Pass Summit 2012 conference, I demoed this in my talk on the 2012 Deployment Model but I didn't cover the parameter part. I believe that last call to be correct but I have not verified it. I have linked to the documentation for the procedure call so that you can tailor it to your specific needs.

Sample PowerShell implementation

Update August 2013

I've learned something at my current client. We have our laptops which are joined to the home domain. We have accounts on the client's AD network. Whenever I need to "do" something in the client's world, I need to launch my process and instruct it to present my "foreign" credentials. What makes this possible is RunAs. Alternate reference

I created a suite of batch files that launch every process I need. They take the form of

runas /netonly:ForeignDomain\Doppelganger "C:\windows\system32\cmd.exe"

I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.

Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).

这篇关于部署SSIS（SQL Server 2012）项目外部网络的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-02 00:27
Powered by LMLPHP ©2024 项目外部网络 0.035749