One Package Two Configurations; App.LoadFromSqlServer(); Package.ImportConfigurationFile(Dev/Prod.dtsconfig);

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


ADVERTISEMENT

Where Should The .dtsconfig File Be Deployed On Dev,Prod Server

Nov 29, 2007

I am migrating from local to Dev,QA and Prod.
I created a .dtsconfig file containing database connection strings to Dev database. What is the "location" on the Dev server where this .dtsconfig file nees to be deployed to??

Thanks for your help.

View 5 Replies View Related

One Package Two Dtsconfig Files

Dec 11, 2007

hi, all;
i'd like to resolve this problem:
We are Developing packages in Dev.Server; if we are Deploying our packages to Prod.Server; we need to modify the connection string and related properties of the connection manager.
1. How to modify the connection manager while deploying to production server?
2. can we create 2 dtsconfig file for a single package; is there any mechanism for instructing sequel server to use a particular dtsconfig file for our package.

i modified the config file in order to change the connection string. but its affecting only the config file not the package. while running the package its reffering the old connection string only.

please provide me some help


Thanks

View 16 Replies View Related

A Good Way To Use One Package In Dev And Prod - Best Practices

May 5, 2008

We have two different SQLServer 2005 databases, one for development and one for prod. I'm pretty new to DTS, and even newer to SSIS, and am working on converting a bunch of DTSs to SSIS.

Our DTS packages essentially were duplicated and edited for production because of the different server names, and some different directory names for sources and destinations. So the dev package would connect to the DBDEV database, and the prod to DBPROD, for example. When I create a package in dev and then copy it to prod, I have to go in and change all of the connections to now point to prod. There are also global variables pointing to various directories that need to be modified. Worse yet, there are also variables to directories set in ActiveX Script Tasks (which are deprecated and so need to be replaced). This is kludgy and error prone. So, since I'm learning SSIS and converting the packages, I would like to make them better.

What is a good way to specify the connections in a dynamic way? That way, when the packages are moved from dev to prod, the prod database can just be specified in one place. In other words, what are some best practices that I should know about? I'm reading this forum, and checking out links I find therein, but I also do better with specific examples (because I am so new to this).

Thank you for any guidance you can provide.
-thursday's geek

View 2 Replies View Related

Integration Services :: How To Pass Multiple Dtsconfig To Child Package Call

Nov 24, 2015

I have a job/step that call a Fileprocessor.dtsx package with 4 different config files. I need to make a enhancement to the job to call the same package using a new (parent) package with multiple iterations based on some business condition.

I am going to modify the job/step to call ParentFileprocessor now with same all 4 config files which in-turn have a call to run Fileprocessor.dtsx; my problem is, how do I pass all 4 config files for child call? Using which task?

I m not allowed to modify Fileproessor.dtsx (to be child pkg) SQL2012, MSDB package deployment model.

View 3 Replies View Related

SSIS Configurations Working In Dev But Not In Prod

Mar 19, 2007

I have a package that uses configurations to override package settings based on what environment the package runs in. The package's configuration entries begins with an initial XML config entry that overrides the package's connection manager to a SQL database that holds the remaining configs in a table. Subsequent config entries then fetch their settings from the table. This package is run from a SQL job.

This all works fine in dev. When I moved everything into prod the packages are not getting configured and are using their values stored in the DTSX files. I've triple checked the XML config file, the tables with the configs, and the packages. There are no error messages. I've added some debuging steps to the package to verify that the configs in the table are not getting into the package.

I've also tried manually changing the configs in the table where the package is set to look if the initial XML config fails to adjust the config database location. The package still fails to see any configs from the table.

What could be different between dev and prod that would produce this situation? Both dev and prod have identical copies of the package and the job and are currently pointed to the same configurations database.

By the way, the other connections in the package work for both source selects and destination inserts. Only the configurations are failing, and again there is no error message.

View 6 Replies View Related

Package Configurations ...

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

Package Configurations

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

Package Configurations

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

Package Configurations

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

SQL Package Configurations

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

Package Configurations

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

Pls Help With Package Configurations

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

Package Configurations Problem

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

Package Configurations Stored In SQL

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

SSIS Package Configurations

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

Managing Package Configurations

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

Dynamic Package Configurations

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

Package Configurations Not So Portable?

Aug 26, 2005

I'm still struggling with finding a sound approach to creating configurations that are truly portable. 

View 43 Replies View Related

SSIS Package Configurations

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

Package Configurations - Some Requests

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

Parent-Child Package Configurations

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

Connection Strings And Package Configurations

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

SQL Server Indirect Package Configurations

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

Package Setup, Configurations And Testing

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

Package Configurations Stored In The Database

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

Direct Vs. Indirect Package Configurations

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

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 View Related

Order In Parent Package Configurations

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

Parent Package Configurations...or Not...What Do You Guys Do?

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

2 Configurations Files In The Deployment Package ?

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

Parent Package Configurations Failing

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

Issue With SSIS Package Configurations In SqlServer

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







Copyrights 2005-15 www.BigResource.com, All rights reserved