"I have built my packages in such a way that different packages can use the same configuration file. For example I have a "Master" configuration file that stores information that all of my packages will need (e.g. connection strings for my log files, warehouse database and metadata database). Our project makes use of many source systems so I also have a configuration file for each source system as well - that way every package that accesses a certain source system can use the appropriate configuration file. .."
How do you create a configuration for each source? I thought the concept of environmental variable is to have one configuration file in a location and defined the path to it in your system environmental variable value?
I have only one config file file for all my 9 packages and I have been having problems.
Maybe I am doing something wrong but what? Can you be more specific about this great idirect configuration?
I am trying to create indirect package configurations so that:
1) My configuration data is stored in a SQL Server table, and
2) The location of that table is stored in an environment variable.
Achieving #1 is easy, but when I hit the "Configuration location is stored in an environment variable", I strike problems. I can specify a environment variable no problems, but there doesn't seem to be anywhere in the documentation to describe the syntax of WHAT should go in that environment variable.
If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?
I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..
My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.
which follows the standard of : db connections, config table, filter
This works by the way on my client but on the dev server the error is:
Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.
I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.
I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)
Appreciate any help. I'm trying to deploy this on the Dev server for testing.
I am trying to get a FK that is derived based on 2 values one of which is a date. But the problem is the corresponding date value is a between meaning the date is between 2 dates.
So translating to SQL it would be:
select * from table 1 join table 2 where table1.key1 = table2.key1 and table1.date between table2.effective_date and table2.expired_date
So is this possible within the SSIS data flow controls?
Normally, you establish referential integrity so that foreign key in one table points to a primary in another. Here is a composite key:
(A_ref, B_ref) => (A, B)
Consider a situation where A is a primary key in table T1. It is refered by T2, which has A,B as it's prmary key. An example of this situation would be a table of printers and table of batches a printer has printed at specific date. The batches are identified by Printer, Date, BatchNo within the date. Now, we create a temporary table T3, which addresses "today" batches. The "today" reference is taken from a date is taken from some record in the DB and, combined with the Printer ID and BatchNo, must point to a record in T2. Is it possible to specify such a complex relationship to ensure the referential integrity?
The advantages of the integirty are:
-- the referred records are pinned down from removal -- it is not possible to refer unexisting object; thus, the referee is ensured to refer an existing one.
I have a series of packages in a project, all have 3-4 configuration files located on my c: drive. I need them to be in a different path on the server. I added another configuration type, but instead of specifying xml file, sql server, etc, I chose the radio button at the bottom that says 'Configuration location is stored in an environment variable'. I created a system variable on my machine, typed it into that box, then saved the configuration. I put the path to my dtsconfig files in that variable. I created the same system variable on the server, called it the same thing, and put the path to my dtsconfig files on the server in that one, but I must be missing something. The packages don't appear to find the config files on the server.
The package configuration organizer has 3 entries of type 'XML Configuration File', followed by one of type 'Indirect XML Configuration File'. Is that the intent of this - to set a system variable that directs the packages to the appropriate directory where the config files are located? Is there something else I need to do? Does SSIS just 'know' that if there is an indirect config file to use that when looking for dtsconfig files? Thanks
I need to configure my package using SSIS Configurations. When I opened SSIS Configuration wizard, press ADD and selected Xml Configuration, I found two options.
One being : Specify configurations directly.
next : config location is stored in env variable.
How to use the second option?[ env variable]. Say I want to configure my connection string for my destination database [ OLEDB Destination Component]. How to do that using second option.
I have some basic understanding of SSIS and now I am looking to take the next step. Especially since I created all my packages with EncryptWithUserKey (I didn't know better) . My assumption is that I place into each of my packages an identically named Environment Variable. Then if I move my configuration files I just need to reassign the value to that variable. Do I have that correct?
For example, I have an XML Configuration file sitting in c:PackConfigs. It is called MyConfig.dtsConfig. How do I create an Environment Variable to point to this configuration file? I assume I create the variable and assign it the value the path of the file (c:PackConfigsMyConfig.dtsConfig). I sort of get that.
But what if I have multiple XML configuration files in my package? And exactly how do I change the variable's value if I move my packages?
I've read some great posts in this forum and at Jamie Thomson's blog http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx but I haven't been able to get to the nitty-gritty that I think I need.
I'm having a problem with indirect configurations on my client's machine.
I've created a package that uses an xml configuration file (.dtsconfig file) that is pointed to by an environment variable. I have this package running on my machine using Windows XP and on my test machine that uses Windows Server 2003. When I set it up on my client's test machine also running Windows Server 2003 the package doesn't recognize the configuration and tries to use the values that it was originally programmed with on my development machine.
I installed the packages the same on both test servers including adding the appropriate environment variables. Is it possible there is something wrong with the environment? I haven't checked the various service pack levels on the two machines. I know that I keep my test machine up to date. I'm not sure about my client. Are there any known issues like this under specific service packs? Or am I just probably missing something glaringly obvious?
I have a set of packages that use an Indirect Configuration to a XML config that gives each package a connection string to the Configuration database. The configuration database has all the connection strings for every connection I use.
I just moved my dbs to a new server. I updated the XML config file to point to the new server and updated the connections in the configuration table. I am having two problems. When I open packages in BIDS, I am receiving errors because the connection strings embedded in the packages were pointing to the old server. I updated the Data Sources in the solution and that didn't fix it. Why is it not using the configurations?
Also, on the production box (64bit), I am having the same problem. It is not reading in the connections from the new SQL configuration table. All my connections that are producing errors are OLEDB for SQL.
Is it possible to create an indirect Package Configuration by using the /CONF switch when running a package via dtexec. Here is what I would like to do: 1. Use an Indirect SQL Server configuration type 2. Instead of storing the connection string in an env. variable, I want to specify it at run time using the /conf switch
In other words, are there any alternatives to storing the configration connection string on the server?
Greetings All; I am having trouble getting an indirect configuration to work. Can someone offer a hand?
Here's the deal.... I have a package the currently has an OLE DB Connection manager. I have an XML configuration file set up to acquire the connection string. That works fine. I have a package variable that contains the absolute path to the .dtsconfig file.
How can I get the connection manager to read the package variable (which has the path to the *.dtsconfig file) instead of the hard coded value?
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate. Thanks, Sarah
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
Hello, I had a question I thought someone might be able to answer before I test my theory.
If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.
If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?
Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?
I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.
How to configure transaction log file size, transactional backup interval and log shipping intereval, exactly? Someone told me it depends on the transaction speed. Is it correct? I have an average transaction speed about 10/min., one day, on SQL 2K/W2K. How should set above issues?
In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.
Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )
Any real time experience (other than referring links) would be really helpful for me.
I have a question about Package Configurations. I had developed a prototype that has connections defined to 2 DEV SQL Servers and runs fine in DEV. I created a Configuration XML file and changed the XML file settings to point to our 2 TEST SQL Servers. I ran this package without issues. However, I was expecting the 2 SQL Server OLE DB connections within BIDS to point to the TEST Server after the package ran. I still was pointing to DEV. Am I missing something here? Thanks in advance.
I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.
Before I started using package configurations, I made several (wrong) assumptions. My primary use for configurations are to move packages between environments (dev, test, prod), so my config files only contain connection information.
My first assumption was I could create 3 different configuration files (XML) with the connection string properties for each environment. Because I use shared data source connections in my packages, these are the only properties I should have to store within my configuration file. Then I use indirect configurations, which I had a system environment variable set up on each server, called SSISConfigPath. On all servers, I have it pointing to a shared folder on our network so us developers can update the configuration files at will, if needed.
Now, in theory if I deploy all of my packages to the SQL Server MSDB on each of the servers, and schedule a job on each server, everything should work, right? Wrong.
First of all, we had to restart the SQL Agent service in order for the package to recognize there is a new system variable (similar to the Indirect Configuration "gotcha" that Jamie blogged about, but in this case, it wasn't a cmd window, but a windows service).
Next, an SSIS package doesn't first read the configuration file, but it tries to validate the connections that were originally stored in the package to begin with before replacing them. This is a problem when the environment you develop on is your local PC (laptop for example), and deploy to production, the SSIS package tries to connect to your laptop PC first, throwing an error because it cannot find it. If it can find your laptop, it succeeds, and only then switches the connection info with what is in the configuration file.
Can this "validation" step be disabled? It seems changing the "Delay Validation" = true on the package doesn't work for connections.
And when you open BIDs to run a package, the "Syncronize Connection Strings" dialog always appears. When you say OK, it doesn't stick and always asks you each time you open the file.
Please let me know what I should be doing, because this doesn't seem to work as flawlessly as I thought it should.
I have been experimenting with both XML and SQL Server Configurations. Management would really like to go with the SQL Server Configurations, but I seem to be having problems with them.
The main goal is to be able to create a configuration for each connection and have every package refer to its specific configuration, therefore in the future we could change say our SA password and only have to update this in a few places rather than for every package.
In order to experiment with the problem I am having I developed 2 identical packages, except one uses SQL Server Configurations and the other uses XML. The configurations save the exact same properties. With the XML package I can go into the configuration file and change say the password and initial catalogue properties and have these changes go into effect. With the SQL Server package these changes do not seem to take place. So I guess what seems to be happening is with the XML package the connectionstring seems to be generated with the other properties that have been saved in the configuration, but in the SQL Server package none of this seems to take place and the connectionstring just stays exactly as it is saved in the configuration file.
I was wondering if I just seem to be missing something or if anybody else has experienced this problem and found a solution.
Within the SSIS package i am currently developing I have a number of variables relating to folder locations. I have added these to a SQL configuration package as i'd like to be able to change these prior to the SSIS package being executed. I plan to launch the package by calling a SQL Agent job from a web page.
On this web page i'd like to be able to change the values in the SQL database so that the package runs with User specified folder locations.
If anyone has done this before i'd appreciate if you could suggest how best to accomplish this. At present i'm thinking of just interogating the created database but as there are no keys etc set up this may not make updating the table easy.
I have an SSIS Package that utilizes two Configurations at design time...
1. Environment Variable Configuration sets the root folder variable and subsequently several other variables within the package using expressions (adapted from Jamie Thompson's template package - thanks Jamie)
2. Indirect XML Configuration to set the connection string to the appropriate db.
I've got 3 environments in play.
1. My workstation, using BIDS to design / debug the package. For my PC, the Indirect Config sets the connection to our test server/database.
2. A test server. For the test server, the Indirect Config on the test server sets the connection string to the test database on that server.
3. A production server. For the production server, the Indirect config on the prod server sets the connection string to the prod database on that server.
Problem...
Running the package in BIDS correctly runs against the test db, no problems.
Running the package on the test server in SSMS (either in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configuration tabs, runs fine against the test db.
Running the package on the prod server in SSMS (again, in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configurations tabs runs the package against the test db.
Is this because I'm not specifying the Configuration I have stored on the production server to use the connection string for production?
Curiously, the Indirect Configuration I have setup to read an environment variable to set the root folder (for logging, etc) works fine without having to specify any configurations on the configuration tabs in SSMS. In fact, I don't think you can specify environment variable configurations on these tabs - they open a Browse window to search the filesystem for a config file.
Any clarification anyone can give me regarding these Configuration tabs, indirect configurations, and what their purpose is would be great. I've spent days researching, reading through the two books I have, etc, and there's not much out there on this.
I created an xml configuration file and specified all the details,i have saved the package with protection level as "dont save sensitive".We are running the package thru autosys.But the package is repeatedly failing,not sure whats wrong with this one.
I've been reading up on package configurations, but I'm not quite sure how to use them correctly. If I am developing a package, I want to connect to a testing server. Then, when it is working correctly, I want to connect to a production server. Are configurations a helpful way of doing this, or would it only be helpful if in the future, I need to make the packages reference a different server?
Greetings,I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.The questions I'm having are:1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?3. If User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)Any recommendations, advice, links, or answers to my questions would be greatly appreciated.
Hi , I am configuring odbc connection at my users place, i tried to change his client configuration , but it is not going in. What was the problem why it is not going in.pls suggest me. i am having sql7. --chowdary
We have been given a new Database server with SAN drives attached for storage. We will see the SAN as 4 separate drives on the server (D,E,F,G). Drives D and E will be a 7 disk array using RAID 5. F and G will be s sperate 7 disk array using RAID 5 also.how best to configure our databases on these drives. I was thinking of splitting the systems db's, user db's, indexes and logs like this:
D: System DBs E: User DBs F: Indexes G: Logs
I wanted to keep data and indexes seperate as well as data and logs seperate. I'm not sure if it makes sense to keep the system dbs on there own filegroup or not.
