SSIS Package Configurations - SQL Server
May 6, 2008
I am using package configurations to hold an email address. I can happily change the email adress when teh config is in an XML file, but when I choose SQL Server to store the package configurations I can change the email address but the changes are not picked up despite coming out of dev studio and back in again - it picks up the default set on the variables tab.
It simply refuses to pick up the email address from the SQL table, but happily creates the dbo.[SSIS Configurations] table with correct entries in the wizard 1st time round.
Any ideas what I am doing wrong ?
View 10 Replies
ADVERTISEMENT
Jan 26, 2006
I have two SSIS projects each with different packages. I have setup the packages with configurations stored in a SQL Server table in MSDB. When I create the configuration on the second project it overwrites all of the first projects configurations. Is there a way to to get two different project configurations stored in the same SQL Server table? Any help would be greatly appreciated. Thanks!
View 6 Replies
View Related
Dec 13, 2007
I have a problem where in I am trying to set up the package in a new environment.
I just need to change the server to which the data flow task will be populating.
I know we have xml configuration file. Is there any way i can read the files from .NET code and change the server name.
Thanks
Sai
View 4 Replies
View Related
Jun 20, 2007
I have a package that executes several other packages. I have an XML packageconfiguration file to set the servername for the parent package, do I need to just add that packageconfiguration to the parent package and the servername in the XML file will be used until all the packages executed from the parent package are executed or do I need to add that configuration file to each lower level package?
Thanks for any help.
View 5 Replies
View Related
Sep 18, 2007
Hello everyone,
I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.
The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)
Config1 - Xml configuration file - for storing the database connection string.
Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.
Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,
However, the package doesn€™t get the configuration settings from the database when I try to execute it as a SQL Agent job.
There aren€™t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.
It works ok, if I change Config2 to be of type XML configuration file.
There could be two problems:
1. SQL server agent doesn€™t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn€™t seem to work.
2. SQL server agent doesn€™t get the configurations in the specified order. In my case,
it could try to read Config2 first, but at that moment it doesn€™t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.
Thanks in advance for your comments.IT
View 12 Replies
View Related
May 8, 2008
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database.
I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again.
This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails.
What do I have to do to make that password actually stick!?
Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio.
Here are at least some of the version information, which may or may not be useful.
Microsoft Visual Studio 2005
Version 8.0.50727.42
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
SQL Prompt 3.5
Thank you for any help you can provide.
-thursday's geek
View 12 Replies
View Related
Jan 24, 2007
Hi:
After moving my deployment folder to the Target Server, I run the Installation Wizard. As I move next, I am missing the window which is supposed to allow me to set package config values as stated in MSDN:
"If the package includes configurations, you can edit updatable configurations by updating values in the Value list on the Configure Packages page."
Source: http://msdn2.microsoft.com/en-us/library/ms141802.aspx
Does anyone know why I am not seeing it? In my deployment bundle which I have moved over has currently 3 files:
1) SSIS Deployment Manifest
2) SSIS Package
3) SSIS Config File
Again, I double click on SSIS Deployment Manifest, and it starts fine. I go thru the steps for File System Deployment, and then it prompts for installation folder path. After that, it takes me directly to validation. Why is it not showing me the Configure Packages Page as described in the MSDN Documentation. Please advise. Thanks.
View 5 Replies
View Related
Mar 22, 2006
Hi all
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.
HELP!!!!
Thanks in advance
Jays
View 3 Replies
View Related
Feb 22, 2008
I tried to follow this link last night (http://curionorg.blogspot.com/) but when i try to configure my package configuration to use sqlserver i recieved the following error message:
The Table "[dbo].[ssisencryptedconfiguration]" cannot be used as a source for SQL Server configurations. The column "ConfiguredVaue" does not have the expected data type.
consult books online for the schema requirements
I followed the blog line by line. i have closed down my project and created a new test package, new project, still the same error. Its there a property somewhere that needs to be configured to allow package configuration to use encryption with sqlserver? I'm running sql 2005 w/sp2. Its there a hotfix that i'm missing. i have not been able to google an answer.
I have been able to successfully use sqlserver as my package configuration. I wanted to see if i could use the encryption code posted on the blog above. i have seen folks reference it or point other folks to it to use for encypting sqlserver package configurations, i just wondered if they have had the same problem.
thanks
View 5 Replies
View Related
Dec 19, 2007
Hi all,
I need one more help!
we can select the dtsconfig file with the Environment variable(indirect configuration);
but i need to select the configuration file at runtime; i've to load the package from the server and apply local configuration file to the package and run in a web server;
Requirements;
1. i have packages with its xml configurations for connection strings alone!
2. i deployed it on the server
3. Trying to execute the packages in a web page onclick event;
We have ASP.net WebPage; in On_Click Event, i have this code;
Application ap = new Application();
Package pk = ap.LoadFromSqlServer("\PROJECT", "itsssqldb", "pmo_package_user", "password", null);
pk.ImportConfigurationFile(@"Packagesdev_staging.dtsConfig");
pk.Execute();
here PROJECT is the Package name; i'm loading the package from the Common Sequel Server 2005; and applying the configuration file to that package and trying to execute this;
but its not using the config file what i mentioned; its returning failure!!
Can you please help me to resolve this issue!,
(The intention of doing this job is to select the configuration file( prod, dev) at runtime)
View 10 Replies
View Related
Feb 6, 2008
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.
View 5 Replies
View Related
Oct 19, 2006
Hi,
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.
Thanks
View 4 Replies
View Related
Feb 12, 2007
Hi,
i keep struggling with dtsconfig.
i have 4 packages which all have the same datasource based on the global-datasource.
is there a way i can save that global-datasource, instead of configuring all 4 packages to use a config-file?
View 6 Replies
View Related
Aug 5, 2007
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.
-Kory
View 8 Replies
View Related
Nov 15, 2006
Hi,
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.
Many thanks,
Grant
View 2 Replies
View Related
Mar 25, 2008
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.
Thanks!
Jamie Pick (not Thompson)
View 9 Replies
View Related
Nov 7, 2007
Hi
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.
Pls can someone help me with this??
Thanks in Advance.
SVGP.
View 6 Replies
View Related
Aug 30, 2006
Hi all,
I have just set up my first deployment utility for the Integration services package i've been building. I seem to be getting problems with the package Configurations. I added an XML config file so that i could change the values of my SQL connection manager at deployment time. This was so that i could deploy it on both a test environment and the live environment. Any other variables can be changed in code by the calling application. As soon as i added the options for the sql connection manager and enabled package configuration i got errors when running the application:
[Execute SQL Task] Error: Failed to acquire connection "InternalProductionData Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
This is before i even deploy the project. If i disable the package configurations everything works as expected. Can anyone help suggest why this might not be working.
Many thanks in advance.
Grant
View 36 Replies
View Related
Jan 3, 2008
I'm relatively new to SSIS, but I have read all the info/threads on Package Config. and I'm really confused.
Our production env. is clustered, and we are running multiple sql instances, so the sys admin does not want me to use Env. Variables or XML files for the package configurations (it's too hard for him to maintain the info across multiple instances, etc). So, I am storing the package configurations in SQL.
Just as a simple test, I used the wizard to create the 1 package configuration for production. The configured value as a complete connection string
When I run the package, it completely ignores the package configuration. How do I change the connections on my tasks to read from the package configuration instead of the connection manager they are currently associated with?
TIA,
Mary Jo
View 5 Replies
View Related
Nov 5, 2007
Hello,
I have a solution with a lot of packages, and each of them has configurations set on an SQL Server table. Right now the information in the table is outdated and I'd like to overwrite it with the new values on the properties of each package (and its elements). Is there another (faster) way to do this without having to disable/enable or recreate all configurations manually?
Thanks in advance
View 7 Replies
View Related
Apr 6, 2007
I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @originalFilePath and @destinationFilePath.
I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.
What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
View 8 Replies
View Related
Aug 26, 2005
I'm still struggling with finding a sound approach to creating configurations that are truly portable.
View 43 Replies
View Related
Sep 5, 2006
To any Microsoft SSIS developers reading this forum, some requests:
1) do NOT allow a package to run if it has "package configurations" enabled and one or more XML files with configurations cannot be found;
2) update the properties window with the properties read from the "package configurations";
3) allow relative paths in the "package configurations" locations (relative to the package location)
Thank you :-)
View 2 Replies
View Related
Sep 15, 2006
Hi All,
May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help
Thanks
View 5 Replies
View Related
Nov 5, 2007
I've been planning on putting all my connection strings for my SSIS packages into package configurations. So far, I've been using XML config files, and these strip out the passwords in connection strings which then have to be retyped every time in notepad.
Has anyone got some better guidance regarding this? Could I use another type of package config to avoid this problem?
My contraints are such that I cannot use integrated security for the packages, so the password MUST be in the connection string. However, I can place the config files in a secure location where only the package and admins have access to them.
View 1 Replies
View Related
Aug 1, 2007
Hi,
I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.
1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.
2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.
3. Should work for deployment.
The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].
There will be one master test package that executes all the tests.
For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?
b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?
I welcome any feedback, suggestions of alternate strategies, etc.
/Anders
View 4 Replies
View Related
Feb 5, 2007
If the configurations are stored in the database then does the package pick up the configuration as and when required or it a one time pick when the package is executed.
What I plan to do?
When the package is executed, I want to have a script task at the start of the package that will update the configuration table in the database with the values that the package needs to run with. I mean change the values of the configurations of the tasks which will be executed after the script task.
Thanks for your time.
$wapnil
View 31 Replies
View Related
Sep 29, 2006
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.
Thanks
View 5 Replies
View Related
May 7, 2007
Hi all,
I am pretty new to SSIS and i found some, (to me) unexpected behaviour. Maybe you guys can help me out understanding it.
I am currently building a multi package ETL solution, that uses parent-package configurations to "distribute" variable values from the "root" package to the lower level and "leaf" packages. Each package contains some 8 parent-package variables.
When i ran the entire solution (i.e. the whole tree) I found that some packages aren't getting the correct values for some of their variables. It seems that if a parent-package configuration that wasn't configured correctly (misspelled parent variable name in this case) blocked the other parent-package configurations in the same package from evaluating. When i moved the correct parent-package configuration to execute prior to the incorrect configuration, it ran just ok.
So apperently parent-package configurations are evaluated one by one, and if one of them fails, the "later" configurations aren't evaluated any more.
Is this a feature? or a bug?
Why don't i get a warning in the error list? Should i maybe configure my BIDS in a different fashion?
Hope someone can help me out.
Cheers,
Tom Kronenburg
View 8 Replies
View Related
Jan 7, 2008
Finding myself in a bit of a lull at work, I'm trying to create some template packages / solutions, encapsulating some of the best practices that get discussed on here.
I'm thinking about passing configurations from Parent to Child packages, something we all must do on a regular basis. I've always done this a particular way, without thinking too much about it, but I'm not sure if this way is the best. I'd be interested in your opinions.
Scenario:
Parent.dtsx calls Child1.dtsx, Child2.dtsx etc, using Execute Package Task.
Parent has variable Source, and variable Destination, populated from a configuration (my preference is SQL table, but whatever).
These variables are ultimately used to populate the ConnectionString property of the source and destination Connection Managers of the Child packages.
Here's the question: Is it better for the Child packages to look up to their parent, and inherit the values of these variables through Parent Package Configurations, or to look directly to the configuration, and pick them up from there? Or does it not matter?
I tend to assign lots and lots of variables in Parent.dtsx, and pull them down into Child.dtsx with Parent Package Configs, but I'm thinking "is this good/bad/unimportant?"
View 7 Replies
View Related
Apr 9, 2007
Hello,
I have two SSIS packages in the same project with, for each one, a (XML) configuration file.
I have created a Deployment Utility file (.SSISDeploymentManifest).
My problem is that, when I execute that utility file, I can't deploy more than one configuration file, the UI only ask for one.
Is anyone can help me ?
Thanks a lot.
Alexis
View 1 Replies
View Related
Jul 26, 2006
Has anyone seen a problem where if you have several parent configurations setup and one fails to find the variable in the parent, gives a warning, and then does NOT load the rest of the parent configruations? I realize order matters in how your configurations are processed, but I wouldn't expect the rest of my configurations to not work simply because it could not find one parent variable.
The problem only seems to come up when I'm dealing with multiple parent configurations. If I'm loading a variable from a config file and then loading the same variable from a parent variable the process works fine. This way I can handle both cases, when I'm debugging it pulls from the config file, when it's running in production it pulls from the parent variable.
View 1 Replies
View Related
Jun 14, 2007
Hi,
I have package on a single server. This packages extracts Data from System A and loads System B, database C. What I'd like to do is use configurations to have this one package loads System B, database C and D.
When a I create two configurations (one pointing at database C and one pointing at database D) and run the package only the last configuration pointing at database D gets loaded.
Anyone know how to get around this and use configurations to load both database C and D?
Thanks,
Mark
View 11 Replies
View Related
