Xforce Keygen 32bits Alias Design 2006
Bethany Pensis
I use the SQL Server package configuration option in my SQL Server Integration Services (SSIS) packages which allows me to use a SQL Server table to store and retrieve SSIS package parameters. I followed the steps in the tip SQL Server Integration Services SSIS Package Configuration and this works great. However, the tip uses an environment variable for storing the connection string for the configuration database and I can't do it this way in my shop. Do you have any ideas on a different way of doing this?
Using an environment variable package configuration for storing the connection string to the SSIS package configuration database is certainly not the most elegant solution as it may require you to restart things that are running like SQL Server Agent when the environment variable is set or changed. Since writing that tip that you referenced I have come up with a better solution - SQL Server Aliases. As the name implies, a SQL Server Alias is a name that you setup that "points" to a SQL Server instance. When you specify the Data Source in your connection string, you use the SQL Server Alias name rather than the server name. If you want to change the server for the SQL Server package configuration table, you simply change the SQL Server Alias.
I'm developing SSIS packages on my laptop and I'm using a SQL Server table (i.e. SQL Server package configuration) to store various package parameters like the connection string to my application database. The SQL Server package configuration table is in the SSIS_CONFIG database running on my laptop. The Data Source in the connection string for the SSIS_CONFIG database is localhost. When I deploy the SSIS packages to a QA or PROD server, the SSIS_CONFIG database may be setup differently; e.g. it may be on a different server, the SQL Server may be a named instance, and so on. The point is I need to be able to easily change the connection string for the SSIS_CONFIG database and without touching any of my SSIS packages.
By setting up a SQL Server Alias, I can set the Data Source property of the SSIS_CONFIG connection string to the SQL Server Alias name. On any machine that I deploy the SSIS packages, I just need to create the SQL Server Alias. If after deployment I change the location of the SSIS_CONFIG database, I just need to change the SQL Server Alias. I don't have to make any changes to the SSIS packages.
There are two tools that you can use to setup a SQL Server Alias: SQL Server Configuration Manager and SQL Server Client Network Utility. The tip How to setup and use a SQL Server alias does a great job of showing how to use both of the tools. However, there is one subtle point that I will cover here. When you are running on a 64 bit machine, you have the option of setting up 64 bit and/or 32 bit SQL Server Aliases. There are two instances that I can think of where you need a 32 bit alias on a 64 bit machine:
There are two versions of the SQL Server Client Network Utility; a 64 bit version and a 32 bit version. When you run on a 64 bit machine, the default is to load the 64 bit version of the utility which only allows you to configure 64 bit aliases. To configure 32 bit aliases you need to run the 32 bit version of the utility which you will find by default in C:\Windows\SysWOW64\CLICONFG.EXE.
Now I'm ready to show how to use the SQL Server Alias in an SSIS package. SQL Server package configuration requires an OLE DB Connection Manager that specifies the SQL Server database where the SSIS configuration table will live. I'm going to create a new database for SSIS package configuration tables; I will name the database SSIS_CONFIG. I'm doing this so that each SSIS "application" can have its own package configuration table and I'll store it in this database. The database name is deliberately the same as the SQL Server Alias; it's not a requirement but too many different names confuse things.
To begin open Business Intelligence Development Studio (BIDS) and create a new SSIS package. Right click in the Connection Managers area of the SSIS package designer and select New OLE DB Connection from the menu. Fill in the dialog as follows:
Note that for Server Name you enter the name of the SQL Server Alias created in the previous section. Select SSIS_CONFIG as the database and click Test Connection to make sure the connection is okay. Click OK to save the connection manager. By default the name of the connection manager is SSIS_CONFIG.SSIS_CONFIG (i.e. SERVERNAME.DATABASENAME). Right click on it and rename it to SSIS_CONFIG.
The Execute SQL task will get the current date and time; this is just enough to verify that we have everything setup correctly. If we do the package will execute successfully; otherwise we'll get some sort of error. The package must connect to the SSIS_CONFIG database to get the package configuration, then connect to the APPLICATION_DATABASE using the connection string that it retrieves from the SSIS_CONFIG table.
If there is an error retrieving the package configuration, you will see the details in the Debug output window. Note that an error retrieving a package configuration does not abort the package. The package will still execute and it will use the value in the package. However, the package may ultimately fail because the value in the package is expected to be set from the package configuration and it wasn't.
As a final step go to the SQL Server Configuration Manager or SQL Server Client Network Utility and change the Server value for the alias. In my case I have a virtual machine running an instance of SQL Server 2005 so I will fill in the IP address as the value for the Server. Since the SSIS_CONFIG OLE DB Connection Manager uses the alias, the package will now retrieve the package configuration for my APPLICATION_DATABASE from the virtual machine without me having to make any changes to my package.
Note that the SSIS_CONFIG database must exist on the machine specified by the SQL Server Alias and the RHOMBUSConfiguration table must be copied to that SSIS_CONFIG database. There are a number of ways to get the package configuration table setup on another server; you can do it manually by scripting the CREATE TABLE statement, issue a SELECT INTO statement if you have a linked server, and so on. Another way would be an SSIS package that conditionally creates the package configuration table on a target database then copies the rows from a source database. Yet another possibility would be to use BCP; it still comes in handy for quick and dirty tasks like copying a package configuration table from one server to another.
Scott, how do you use the view that returns the hostname to connect to the configuration table? I understand how we can have a static connection to the config db using an alias in Ray's solution but I dont know how to use a view in that connection object which connects to the config table?
My solution was to create a base table for SQL configuration that allowed multiple values for each item, with an additional field that specifies whether the value is for production, stage, test, or development environments (or a global value for all environments). There is a table of server names that specify which environment each server belongs to, and a view that uses the HOST_NAME() function to select the values for that environment.
So a package running on server P in the production environment gets the production configuration values, while the same package running on server T in the test environment gets test configuration values. If anyone loads a package into the IDE on their laptop for debugging, they get development configuration and can be confident that they can test it without touching production data or sending embarassing emails to customers.
All packages are self-contained, there are no XML files, environment variables, aliases, or other artifacts to deploy. All packages have a fixed, unconfiguered connection to the configuration database.
They we handle this in our shop is at the first of each SSIS package a task reads an XML file on the local server where the package is running to get the storage location of the SSIS configuration database/table.
Regardless of which decision we choose, there will be some cases where casts are needed to convert from one type to another. Casts that truncate values can be very harmful and lead to portability issues, and if programmers find themselves needing to cast very often, they are unlikely to have clarity about what the original types were trying to express. This means that we should try to find solutions that keep casts to a minimum, even though we cannot avoid them entirely.
An orthogonal, but related design decision is whether we should allow implicit coercion from one integer type to a larger integer type. This reduces the incidence of casting, and should not generally be dangerous, but the precise allowed coercions might differ from platform to platform (e.g. whether a machine-sized integer can be coerced to an i32 would be different on 32-bit or 64-bit systems).
The design proposed in the earlier discuss post aimed to compromise between the current definition of int and a widespread desire for i32 to be the default type by keeping int as machine-sized, but recommending i32 for general (non-indexing) use.
One solution to the problem is simply not to have an int type (but instead have the sized integer types plus something like isize). Instead, whenever you want to use an integer, it is up to you to figure out what the best size for that usage is.
At first glance, one way to further mitigate overflow problems across all architectures (for integers used without much thought) is to make int an alias for an even bigger number, like i64 or a BigInt.
Unfortunately, if the program then attempts to use the default int as an index, it will be forced to cast it down to the size of the index. In practice, this would result in more widespread, dangerous (truncating) casting, which would reintroduce another kind of pernicious, platform-specific overflow problem. It would also significantly harm ergonomics.
b37509886e