I have an SSIS Package that sets some variable data from a SQL Server Package Configuration Table. (Selecting the "Specify configuration setings directly" option)
我有一个SSIS包,它从SQL Server包配置表中设置一些可变数据。 (选择“直接指定配置设置”选项)
This works well when I'm using the Database connection that I specified when developing the package. However when I run it on a server (64 bit) in the testing environment (either as an Agent job or running the package directly) and I Specify the new connection string in the Connection managers, the package still reads the settings from the DB server that I specified in development.
当我使用我在开发包时指定的数据库连接时,这很有效。但是,当我在测试环境中的服务器(64位)上运行它(作为代理作业或直接运行程序包)并且我在Connection管理器中指定新的连接字符串时,程序包仍然从数据库服务器读取设置我在开发中指定的。
All the other Connections take up the correct connection strings, it only seems to be the Package Configuration that reads from the wrong place.
所有其他Connections占用正确的连接字符串,它似乎只是从错误的位置读取的包配置。
Any ideas or am I doing something really wrong?
任何想法或我做错了什么?
9 个解决方案
#1
2
The only way I was able to do this was to use Windows Environment Variables. You can specify things like connection strings and user preferences in environment variables, and then pick up those environment variables from your SSIS Task.
我能够做到这一点的唯一方法是使用Windows环境变量。您可以在环境变量中指定连接字符串和用户首选项等内容,然后从SSIS任务中选择这些环境变量。
#2
1
I prefer to use Server Aliases in the SQL Client Configuration. That way, when you decide to point the package to another SQL Server it is as simple as editing the alias to point to the new server, no editing necessary in the SSIS package. When moving the package to a live server, you need to add the aliases, and it works.
我更喜欢在SQL客户端配置中使用服务器别名。这样,当您决定将包指向另一个SQL Server时,就像编辑别名以指向新服务器一样简单,SSIS包中无需编辑。将程序包移动到实时服务器时,需要添加别名,并且它可以正常工作。
This also helps when you have a real painful naming convention for servers, the alias can be a more descriptive name than the actual machine name.
当您对服务器有一个真正痛苦的命名约定时,这也有帮助,别名可以是比实际机器名更具描述性的名称。
#3
1
I didn't actually understand your question completely but I store my connection settings in a configuration files usually one for each environment like dev, production etc. The packages read the connection settings from the config files when they are run.
我实际上并没有完全理解你的问题,但是我将连接设置存储在一个配置文件中,通常每个环境都有一个用于dev,production等。这些包在运行时会从配置文件中读取连接设置。
#4
1
When you're creating a job to call the SSIS package, and you're setting up the step, there is a tabbed area. The default tab is where you set the package name, and the next tab over is where you can set the configuration file. Have a config file for each package, and change for the server (dev, test, prod). The config file can be put directly on the dev, test, and prod servers, and then point to them when setting up that job.
当您创建作业以调用SSIS包时,如果您正在设置该步骤,则会有一个选项卡区域。默认选项卡是您设置包名称的位置,下一个选项卡是您可以设置配置文件的位置。为每个包都有一个配置文件,并为服务器(dev,test,prod)进行更改。配置文件可以直接放在dev,test和prod服务器上,然后在设置该作业时指向它们。
#5
1
If u are using SQL Server Package Configuration then all the properties of the packages will come from SQL Server table - Please check that
如果您正在使用SQL Server包配置,那么包的所有属性将来自SQL Server表 - 请检查它
#6
0
SSIS security the way it stands is terrible. No one will be able to support things when I am out of the office. The job never reads from the configuration file...I give up. It only works when I edit the string in the Data sources tab. However the password gets lost if you happen to go into the job a second time. Terrible design, absolutely horrible. You would think that when you specify a xml file in the job step it would read the connection string from there that is defined, but it does not. Does this really work for anyone else?
SSIS安全性的方式很糟糕。当我不在办公室时,没有人能够支持。作业永远不会从配置文件中读取...我放弃了。它仅在我在“数据源”选项卡中编辑字符串时有效。但是,如果您第二次碰巧上班,密码就会丢失。糟糕的设计,绝对可怕。您可能会认为,当您在作业步骤中指定xml文件时,它将从那里读取已定义的连接字符串,但事实并非如此。这真的适合其他人吗?
#7
0
Goto the package properties and set deployment True. This should work for what you have done.
转到包属性并将部署设置为True。这应该适用于您所做的事情。
#8
0
I had the identical question, and got the same answer, i.e. you cannot edit the connection string used for package configurations hosted in SQL Server, except if you specify that the SQL Server connection string should be in an environment variable.
我有相同的问题,得到了相同的答案,即您无法编辑用于SQL Server中托管的程序包配置的连接字符串,除非您指定SQL Server连接字符串应该在环境变量中。
This unfortunately does not work in my dev setup, where two environments are hosted on the same machine. I ended up following Scott Coleman's approach as detailed on SQL Server Central [Free sign-up and a good site]. The trick is that you create a view to store your configuration settings on one central server, and then use the machine that connects to it to determine which environment is active.
遗憾的是,这在我的开发设置中不起作用,其中两个环境托管在同一台机器上。我最终遵循了Scott Coleman的方法,详细介绍了SQL Server Central [免费注册和一个好网站]。诀窍是您创建一个视图以将配置设置存储在一个*服务器上,然后使用连接到它的计算机来确定哪个环境是活动的。
I used that approach, but also used the User connecting to the environment to make a determination, because my test and dev setups run on the same SSIS instance, but as different user names. Scott suggests in the comments that the application name should be set, but this cannot be changed in the package execution job step, so it was not an option.
我使用了这种方法,但也使用连接到环境的用户来做出决定,因为我的测试和开发设置在同一个SSIS实例上运行,但是作为不同的用户名。 Scott在评论中建议应该设置应用程序名称,但是这不能在包执行作业步骤中更改,因此它不是一个选项。
One other caveat that I found was that I had to add "Instead of" triggers to my view to do the inserts, updates and deletes for configuration variables.
我发现的另一个警告是我必须在我的视图中添加“而不是”触发器来执行配置变量的插入,更新和删除。
#9
0
We want to keep our package configs in a database table, we know it gets backuped with our other data and we know where to find it. Just a preference.
我们希望将我们的包配置保存在数据库表中,我们知道它与我们的其他数据一起备份,我们知道在哪里可以找到它。只是一个偏好。
I have found that to get this to work I can use an environment variable configuration to set the connection string of the connection manager that I am reading my package config from. (Although I had to restart the SQL Server agent before it could find the new environment variable. Not ideal when I deploy this to Production)
我发现要使其工作,我可以使用环境变量配置来设置我正在读取我的包配置的连接管理器的连接字符串。 (虽然我必须重新启动SQL Server代理才能找到新的环境变量。当我将它部署到Production时不太理想)
Looks Like when you run an SSIS package as a step in a scheduled task it works in this order:
看起来当您将SSIS包作为计划任务中的一个步骤运行时,它按以下顺序运行:
- Load each of the Package Configs in the order they appear in the Package Configuations Organiser
- Set the Connection Strings from the Data sources tab in the Job Step properties of the Scheduled Job
- Start running package.
按照它们在Package Configuations Organizer中出现的顺序加载每个Package Configs
从“计划作业”的“作业步骤”属性中的“数据源”选项卡设置“连接字符串”
开始运行包。
I would have expected the first 2 to be the other way around so that I can set the data source for my package config from the scheduled job. That is where I would expect other people to look for it when maintaining the package.
我原本期望前两个是相反的,这样我就可以从预定的作业中为我的包配置设置数据源。这是我希望其他人在维护包时寻找它的地方。
#1
2
The only way I was able to do this was to use Windows Environment Variables. You can specify things like connection strings and user preferences in environment variables, and then pick up those environment variables from your SSIS Task.
我能够做到这一点的唯一方法是使用Windows环境变量。您可以在环境变量中指定连接字符串和用户首选项等内容,然后从SSIS任务中选择这些环境变量。
#2
1
I prefer to use Server Aliases in the SQL Client Configuration. That way, when you decide to point the package to another SQL Server it is as simple as editing the alias to point to the new server, no editing necessary in the SSIS package. When moving the package to a live server, you need to add the aliases, and it works.
我更喜欢在SQL客户端配置中使用服务器别名。这样,当您决定将包指向另一个SQL Server时,就像编辑别名以指向新服务器一样简单,SSIS包中无需编辑。将程序包移动到实时服务器时,需要添加别名,并且它可以正常工作。
This also helps when you have a real painful naming convention for servers, the alias can be a more descriptive name than the actual machine name.
当您对服务器有一个真正痛苦的命名约定时,这也有帮助,别名可以是比实际机器名更具描述性的名称。
#3
1
I didn't actually understand your question completely but I store my connection settings in a configuration files usually one for each environment like dev, production etc. The packages read the connection settings from the config files when they are run.
我实际上并没有完全理解你的问题,但是我将连接设置存储在一个配置文件中,通常每个环境都有一个用于dev,production等。这些包在运行时会从配置文件中读取连接设置。
#4
1
When you're creating a job to call the SSIS package, and you're setting up the step, there is a tabbed area. The default tab is where you set the package name, and the next tab over is where you can set the configuration file. Have a config file for each package, and change for the server (dev, test, prod). The config file can be put directly on the dev, test, and prod servers, and then point to them when setting up that job.
当您创建作业以调用SSIS包时,如果您正在设置该步骤,则会有一个选项卡区域。默认选项卡是您设置包名称的位置,下一个选项卡是您可以设置配置文件的位置。为每个包都有一个配置文件,并为服务器(dev,test,prod)进行更改。配置文件可以直接放在dev,test和prod服务器上,然后在设置该作业时指向它们。
#5
1
If u are using SQL Server Package Configuration then all the properties of the packages will come from SQL Server table - Please check that
如果您正在使用SQL Server包配置,那么包的所有属性将来自SQL Server表 - 请检查它
#6
0
SSIS security the way it stands is terrible. No one will be able to support things when I am out of the office. The job never reads from the configuration file...I give up. It only works when I edit the string in the Data sources tab. However the password gets lost if you happen to go into the job a second time. Terrible design, absolutely horrible. You would think that when you specify a xml file in the job step it would read the connection string from there that is defined, but it does not. Does this really work for anyone else?
SSIS安全性的方式很糟糕。当我不在办公室时,没有人能够支持。作业永远不会从配置文件中读取...我放弃了。它仅在我在“数据源”选项卡中编辑字符串时有效。但是,如果您第二次碰巧上班,密码就会丢失。糟糕的设计,绝对可怕。您可能会认为,当您在作业步骤中指定xml文件时,它将从那里读取已定义的连接字符串,但事实并非如此。这真的适合其他人吗?
#7
0
Goto the package properties and set deployment True. This should work for what you have done.
转到包属性并将部署设置为True。这应该适用于您所做的事情。
#8
0
I had the identical question, and got the same answer, i.e. you cannot edit the connection string used for package configurations hosted in SQL Server, except if you specify that the SQL Server connection string should be in an environment variable.
我有相同的问题,得到了相同的答案,即您无法编辑用于SQL Server中托管的程序包配置的连接字符串,除非您指定SQL Server连接字符串应该在环境变量中。
This unfortunately does not work in my dev setup, where two environments are hosted on the same machine. I ended up following Scott Coleman's approach as detailed on SQL Server Central [Free sign-up and a good site]. The trick is that you create a view to store your configuration settings on one central server, and then use the machine that connects to it to determine which environment is active.
遗憾的是,这在我的开发设置中不起作用,其中两个环境托管在同一台机器上。我最终遵循了Scott Coleman的方法,详细介绍了SQL Server Central [免费注册和一个好网站]。诀窍是您创建一个视图以将配置设置存储在一个*服务器上,然后使用连接到它的计算机来确定哪个环境是活动的。
I used that approach, but also used the User connecting to the environment to make a determination, because my test and dev setups run on the same SSIS instance, but as different user names. Scott suggests in the comments that the application name should be set, but this cannot be changed in the package execution job step, so it was not an option.
我使用了这种方法,但也使用连接到环境的用户来做出决定,因为我的测试和开发设置在同一个SSIS实例上运行,但是作为不同的用户名。 Scott在评论中建议应该设置应用程序名称,但是这不能在包执行作业步骤中更改,因此它不是一个选项。
One other caveat that I found was that I had to add "Instead of" triggers to my view to do the inserts, updates and deletes for configuration variables.
我发现的另一个警告是我必须在我的视图中添加“而不是”触发器来执行配置变量的插入,更新和删除。
#9
0
We want to keep our package configs in a database table, we know it gets backuped with our other data and we know where to find it. Just a preference.
我们希望将我们的包配置保存在数据库表中,我们知道它与我们的其他数据一起备份,我们知道在哪里可以找到它。只是一个偏好。
I have found that to get this to work I can use an environment variable configuration to set the connection string of the connection manager that I am reading my package config from. (Although I had to restart the SQL Server agent before it could find the new environment variable. Not ideal when I deploy this to Production)
我发现要使其工作,我可以使用环境变量配置来设置我正在读取我的包配置的连接管理器的连接字符串。 (虽然我必须重新启动SQL Server代理才能找到新的环境变量。当我将它部署到Production时不太理想)
Looks Like when you run an SSIS package as a step in a scheduled task it works in this order:
看起来当您将SSIS包作为计划任务中的一个步骤运行时,它按以下顺序运行:
- Load each of the Package Configs in the order they appear in the Package Configuations Organiser
- Set the Connection Strings from the Data sources tab in the Job Step properties of the Scheduled Job
- Start running package.
按照它们在Package Configuations Organizer中出现的顺序加载每个Package Configs
从“计划作业”的“作业步骤”属性中的“数据源”选项卡设置“连接字符串”
开始运行包。
I would have expected the first 2 to be the other way around so that I can set the data source for my package config from the scheduled job. That is where I would expect other people to look for it when maintaining the package.
我原本期望前两个是相反的,这样我就可以从预定的作业中为我的包配置设置数据源。这是我希望其他人在维护包时寻找它的地方。