首页 \ 问答 \ SSIS 2012 - 项目包环境连接配置的最佳模式(SSIS 2012 - Best pattern for project-package-environment connection configuration)

SSIS 2012 - 项目包环境连接配置的最佳模式(SSIS 2012 - Best pattern for project-package-environment connection configuration)

 在SSIS 2012项目中配置连接的“最佳实践”方法是什么（将部署到服务器）？ 文档和Google显示了实现此目标的多种方法，但哪种方式可以充分利用2012项目/部署模型，并且可以轻松定制，维护等？  
 考虑一个带有三个包的项目（NorthwindETL），每个包引用localhost.Northwind。 在SSIS服务器上，有一个项目'NorthwindETL'和一个Environment'Dev'。  
 要配置连接，我有以下选项  
 
  IN BIDS ：在每个包中手动硬编码连接（通过连接管理器）。 IN SSIS ：在连接管理器选项卡下配置SSISDB'NorthwindETL'项目，修改Northwind连接字符串，每个包一次。  
  IN BIDS ：使用Connection Manager的“参数化...”选项，创建项目（或包）参数以指定连接字符串（Northwind_Conn）。 IN SSIS ：配置SSISDB'NorthwindETL'项目，指定Northwind_Conn参数值。  
  IN BIDS ：创建项目级别的Connection Manager（Project_Northwind_Conn）。 IN SSIS ：配置配置SSISDB'NorthwindETL'项目，在连接管理器选项卡下，修改'Project_Northwind_Conn'连接字符串。  
  IN SSIS ：在SSISDB上创建一个名为“DEV”的环境。 在'Dev'环境属性中，在变量下创建一个变量'Env_Northwind_Conn'。 配置NorthwindETL项目，将'Northwind_Conn'设置为环境变量'Env_Nothwind_Conn'  
 
 （另外，我更喜欢一种允许我们单独指定项目的解决方案，例如InitialCatalog和Server，但这不是必需的。虽然连接管理器允许您修改InitialCatalog和Server属性，但这似乎并没有实际修改ConnectionString 。） 

What is the 'best practices' way to configure connections in SSIS 2012 project (that will be deployed to the server)? Documentation and Google shows multiple ways to accomplish this, but which way takes full advantage of the 2012 project/deployment model and is easily customizable, maintainable etc?  
Consider a project (NorthwindETL) with three packages with each package referencing localhost.Northwind. On SSIS server, there is a project 'NorthwindETL', and an Environment 'Dev'.  
To configure the connection, I have the following options 
 
 IN BIDS: Hard code the connection (via connection manager) manually in each package. IN SSIS: Configure the SSISDB 'NorthwindETL' project, under the connection manager tab, modify the Northwind connection string, once for each package. 
 IN BIDS: Using the Connection Manager 'Parameterize…' option, create a project (or package) parameter to specify the connection string (Northwind_Conn). IN SSIS: Configure the SSISDB 'NorthwindETL' project, specify the Northwind_Conn parameter value. 
 IN BIDS: Create a project level Connection Manager (Project_Northwind_Conn). IN SSIS: Configure the Configure the SSISDB 'NorthwindETL' project, under the connection manager tab, modify the 'Project_Northwind_Conn' connection string. 
 IN SSIS: Create an Environment on SSISDB called 'DEV'. In the 'Dev' environment properties, under variables, create a variable 'Env_Northwind_Conn'. Configure the NorthwindETL project, set 'Northwind_Conn' to the environmental variable 'Env_Nothwind_Conn'  
 
(Also, I would prefer a solution that allows us to specify items separately such as InitialCatalog and Server, but this is not necessary. Although the connection manager allows you to modify the InitialCatalog and Server properties, this does not seem to actually modify the ConnectionString.) 

原文：https://stackoverflow.com/questions/27848630

更新时间：2022-03-14 14:03

最满意答案

 未命名的触发器是由于一个表与另一个表的引用完整性（外键）关系。  
 重现步骤：  
 步骤1：创建两个表，其中一个表引用其他表，并在这些表中创建一些测试行。 T1可以使用CASCADE OR RESTRICT进行删除或更新。  
CREATE TABLE T (id NUMBER);
CREATE TABLE T1 (id NUMBER REFERENCES T (id) DELETE (CASCADE/RESTRICT) UPDATE ( CASCADE /RESTRICT ));
 
 第2步：编写一个创建sqlite3连接的测试C ++程序。 有关SQLite C / C ++接口的更多信息，请参阅https://www.sqlite.org/cintro.html 。  
 第3步：使用sqlite3_exec启用以下操作  
PRAGMA FOREIGN_KEY=ON . 
 
 步骤4：使用sqlite3_trace注册回调，该回调在回调中打印查询。 请参阅： https ： //www.sqlite.org/c3ref/profile.html  
 步骤5：调用execute方法更新表T的id。  
 输出：上面的语句将执行表T的UPDATE和T的引用表。在这种情况下，它的T1。 表T1上的更新生成未命名的触发器，并在sqlite3_trace上生成回调。 回调中的sql没有关于此触发器的信息，因此输出如下所示，即没有名称的触发器：  
TRIGGER - 
 
 
  结论：由于外键关系，可以看到未命名的触发器。 修改引用的表时，会尝试更改其关联的表，从而导致sqlite3_trace回调中出现未命名的触发器。  
  注意：每个引用都会有一个未命名的触发器。 因此，如果在n个表中引用了一个字段，您将在sqlite3_trace上看到n个未命名的触发器和n个回调。 此外，数据库应该具有PRAGMA FOREIGN_KEY ON，以便它强制实施参照完整性。 如果PRAGMA FOREIGN_KEY为OFF（0），则不会看到此行为。  

The un-named triggers are because of the referential integrity ( foreign key ) relationship of one table with another table. 
Steps to reproduce: 
Step1 : Create two tables where one table references other table and create some test rows in these tables. T1 can have CASCADE OR RESTRICT for delete or update. 
CREATE TABLE T (id NUMBER);
CREATE TABLE T1 (id NUMBER REFERENCES T (id) DELETE (CASCADE/RESTRICT) UPDATE ( CASCADE /RESTRICT ));
 
Step 2: Write a test C++ program that creates a sqlite3 connection. Refer https://www.sqlite.org/cintro.html for more on SQLite C/C++ Interface. 
Step 3: Enable following using sqlite3_exec 
PRAGMA FOREIGN_KEY=ON . 
 
Step 4: Register a callback with sqlite3_trace that prints the query in the callback. Refer : https://www.sqlite.org/c3ref/profile.html 
Step 5: Call execute method to update id of table T.  
Output: Above statement will execute UPDATE of table T and on referenced tables of T. In this case, its T1. The update on table T1 generates un-named trigger and a callback is generated on sqlite3_trace. The sql in callback has no information about this trigger and hence output is as below , i.e. trigger with no name: 
TRIGGER - 
 
 
 Conclusion : The un-named triggers are seen because of the foreign key relationship. When a referenced table is modified, its associated tables are attempted to change causing un-named triggers in sqlite3_trace callbacks. 
 Note : There will be an un-named trigger for each reference. So, if a field is referenced in n tables , you will see n un-named triggers and n callbacks on sqlite3_trace. Also, the database should have PRAGMA FOREIGN_KEY ON so that it enforces referential integrity. You will not see this behavior if PRAGMA FOREIGN_KEY is OFF ( 0 ).

SSIS 2012 - 项目包环境连接配置的最佳模式(SSIS 2012 - Best pattern for project-package-environment connection configuration)

最满意答案

相关问答

在sqlite_trace回调函数上没有名称的触发器(trigger with no name on sqlite_trace callback function)[2023-08-24]

在C ++中使用SQLite：对象函数作为回调(Using SQLite in C++: object function as a callback)[2022-09-07]

为什么即使使用跟踪和回调函数GUI也不会更新？(Why doesn't the GUI update even with trace and a callback function?)[2021-11-11]

SQLITE 3如何插入在触发器子句Python中包含自定义函数(SQLITE 3 How to Insert include a Custom Function inside a trigger clause Python)[2023-12-17]

callback = callback ||(callback = callback || function() {}; [duplicate])[2024-04-29]

promisifyAll函数在回调中没有堆栈跟踪(no stack trace on callback in promisifyAll function)[2023-07-03]

如何编写和包装ctypes一个python函数作为sqlite3的回调需要？(How to code and wrap with ctypes a python function needed as callback by sqlite3?)[2022-02-16]

用于记录的SQLite跟踪(SQLite trace for logging)[2022-11-08]

SQLite UDF - VBA回调(SQLite UDF - VBA Callback)[2022-03-24]

sqlite3_exec中的回调函数（C API）(Callback function in sqlite3_exec (C API))[2022-03-01]

相关文章

最新问答