I am hoping someone with more experiencing deploying packages from dev to other environments can help me out here. When developing in BIDS everything works fine. When I deploy I want to use a configuration file. This does seem to work fine by specifying the configuration file on the DTEXEC command. However, if it is specified incorrectly the package STILL executes using the information it was created with. I am attempting to control things like connection strings and passwords via configuration files so that I can take the same package and deploy to different locations and have it run against different sources and destinations. What I am afraid of is that things could easily get screwed up if the configuration file gets moved or renamed, or if the dba that is scheduling the job (outside of sql agent using a different job scheduler on a machine that only has the SSIS service) messes up typing the config file name. Shouldn't it fail if you specify an invalid parameter. Isn't there a way to make it do that? Any help or insight is appreciated. BTW I have used environment variables as well and the issue is the same. If it cannot locate it, it still will execute.
I am deploying SSIS packages using the deployment utility. We are starting to use configuration files for storing connection information. I have the configuration files in a directory on the server I am deploying the packages to, I want all new packages deployed to use these files. When I deploy a new package it wants to overwrite the config files on the server with the ones in the package. I have tried to remove the .dtsconfig file from the package deployment folder but then the package will not use the config files on the server. How do you deploy a package and get it to use the .dtsconfig files that are already on the server?
We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (
I searched for a viable answer, work-around, or hotfix that would address this issue but found none.
The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.
The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.
Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.
Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?
I'm working on a web application that uses SQL Server 2000. Once I'm ready to upload my website to a production server, I need to know how to upload my SQL Server database files to the server. Are there actual files that can be uploaded? I've only deployed a website that works with MySQL to a production server, and there were no files to upload... the database had to be created on the server, and then the data itself had to be exported to text files (on my computer), then imported/uploaded to the production server using SQL statements...
Hi..I would not like my customers to have access to any SQL code that isbeing packaged with our product and hence would like to encrypt it insome fashion. What's your recommendation on packaging/deploying .sqlfiles?Would appreciate any feedback.Thanks,Sandeep Madduri.
I am working on on SQL Server Integration Services and facing few problems in it.
Actually am supposed to create a package that would automatically pick excel spreadsheets with a specific format and import it onto the SQL server. (Lets say , there is a company named AB and they have got various products named CD,EF and GH and each product has its own spreadsheet in which its monthly sales(JAN, FEB,...NOV, DEC) is given. So i have to build a generic package for each product (lets say CD) so that i don't have to import every spreadsheet seperately for each month. To summarize i just have to build a package where i can deploy the various spreadshhets again and agian instead of building a package for each and every month spreadsheet seperately.
I have tried and used lots of combinations like Loop conatiners etc. but still am not able to find a solution to it.
I generated and transfered reports using RSscripter. I created those reports in my local machine. Then I transfered to prodcution machine. But the problem is there is no Visual Studio in prodcution machine. So I'm wondering how to deploy those reports now. Can any one tell me how to do this please?
I've written a ClickOnce app that deploys a Sql Server Express data file (.mdf) and log file (.ldf) along with the other application files. SQL Express SP2 is a prerequisite for this app. After the applicaiton installs and starts up it copies these files to a specific folder and attaches them by calling the AttachDatabase() method of a Microsoft.SqlServer.Management.Smo.Server object.
This method works fine using Windows XP, however when I run it on Windows Vista I get the following exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'localhostSQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Unable to open the physical file "C:WashMasterDatabasewasshmaster.mdf". Operating system error 5: "5(Access is denied.)".
This exception occurs when I try to programmatically attach the database.
I've searched through the MSDN forums for a solution and I've only found two suggestions:
1.) Create all database connections using a User Instance. 2.) Create the database (using SQL) in the target folder, rather than copying it over from the deployment folder.
I don't like the idea of using User Instances because my entire data access layer is currently written using Ole and I would rather not rewrite it if I don't have to. I cannot find any way to use User Instances with Ole.
I also don't like the idea of creating the database using SQL because it is fairly large.
Here are my questions:
1.) Can User Instances be used with Ole?
2.) Can I somehow programmatically set the permissions of the copied data and log files so that SQL Express can access them without error?
3.) Is there a better method for doing what I'm trying to do or am I destined to have to either rewrite in data access layer so I can use User Instances or create my large database rather than copy it to the target directory?
I am having a hard time grasping how to use configuration files.
Here's an example:
I created a simple package (one execute sql task) that simply does a "select * from xyz". Table xyz only exists on Server 1.
I then created a configuration file, the only setting being the ole db connection manager which is pointing to Server 1. I then went into the file and added the "password=zzz" since it's a sql server login.
When I run the package it worked.
Next, I went into the configuration file I just created and had it point to another server, Server 2, thinking this would now cause the execute sql task to fail since table xyz does not exist on Server 2.
But it's still running succesfully. What's the deal here? Shouldn't changing the configuration file settings cause the package to now fail?
Hi all, I am using XML Configuration Files to configure my package connections, but when i use them on a Database Transfere Task every time i try to open those packages on a different machine from the one in which it was created i got the following error:
Validation error. «Connection_name» : The Validate method on the task failed, and returned error code 0x80131501 (Failed to connect to server «Defined Server».). The Validate method must succeed and indicate the result using an "out" parameter. «Package Name»
I€™m having some issues associated with the configuration files that I can€™t understand:
- Having a project with several packages using the same connections in each one (some packages use all connection and others I just have some of them), why can€™t I use only one configuration file for all project. Assuming that I would have to configure all packages at least I would like to use always the same file. On the other way I would need 100€™s of configuration files (one per package) to configure always the same connection, just because not all the packages use exactly the same connections.
- Having a configuration file on a machine, the package saves the full path, even if the configuration file lays in the same path them the project. When I change my project to another machine that doesn€™t use the same file structure forces me to open every package and change the configuration file location.
Having 100€™s of packages and a team of developers, even using the source safe, this makes the task just a little difficult
I have a package with a corresponding configuration file.
If I open Sql server management studio and i connect to SSIS and right click to import a package, and then select to store the package in SQL Server (not on the file system)....
What happens with the configuration file?
Does the import take the values from the configuration file and place them in the package which then is stored in SQL Server?
Or do I need to put the configuration file someplace on the SQL Server where the package is imported so it can access it when it runs?
I'm a bit confused about what goes on there?
For example, I tried using the build command and then running the manifest file to import using the wizard and when it does that it copies the configuration files to a default location within the c:program filesmicrosoft sql server directory.
I am having a bit of trouble with SSIS Configurations.
- In BIDS, I have added a configuration file and specified the properties I want to expose. - When I build the project, I get the standard bindeployment folder which contains the package file (.dtsx), the configuration file (.dtsconfig) and the deploymentmanifest. - Before deploying the package, I edit the config file to have the settings I want it to for the environment I am deploying to. - The package deploys OK - When I work directly on the SSIS server (64 bit), I can go into SQl Mgt Studio, choose 'run package' and when I look in the connection manager window all the settings are as I desire ( I havent had to add a configuration file manually) - When I work on a client machine, I connect to the SSIS server and choose 'run package' - the properties/connections are the same as on the server but the values themselves are completely different.
Why is this? Why when I run the package from the client (32 bit) do the configuration values appear to be completely different? How can I run the package remotely and pick up the configuration values that I deployed with? Or have I misunderstood this whole configuration function?
I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////
Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need
I installed RS2K5 on cluster, modified web.config and rswebapplication.config according to 'Configuring URL settings and View State Validation' in sql2k5 books online. Web browser seems not happy with the change of rswebapplication.config (refer to following error). Anyone has idea why? Thanks. Server Error in '/Reports' Application.
The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[Exception: The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element.] Microsoft.ReportingServices.Diagnostics.RSConfiguration.ThrowInvalidFormat(String element) +41 Microsoft.ReportingServices.Diagnostics.UIConfiguration.ParseXML(XmlNode node, RSConfiguration configObject) +1135 Microsoft.ReportingServices.Diagnostics.RSConfiguration.ParseDocument() +972 Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() +32
[ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.] Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() +166 Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) +62 Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) +296 Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) +134 Microsoft.ReportingServices.UI.Global.get_ConfigurationManager() +84 Microsoft.ReportingServices.UI.Global.get_Configuration() +4 Microsoft.ReportingServices.UI.GlobalApp.Application_AuthenticateRequest(Object sender, EventArgs e) +84 System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Can someone tell me why path to the configuration file couldn't be relative to the package? When I try to use relative path (i.e. do not specify full path to the configuration file) package then run from BIDS looks for configuration file in the solution folder instead of package's folder (project folder).
Is there ane way to force package use same folder to search for caonfiguration files?
Please forgive me if my question is pre-emptive (as in, I haven't throughly researched this yet), but I'd like to know in advance the following question:
Q. When scheduling SSIS jobs to run packages (in msdb), do I specify a configuration file in the job? Is this how one uses the configuration file? Or is the configuration file "configured" at the SSIS package level, before you schedule the job? Or both?
Also, is there a good link on this topic? I'm already afraid I'm going to be confused!
http://blogs.conchango.com/jamiethomson/default.aspx has a lot of great tid-bits for SQL 2005. I am currently on a tight deadine for 25 SSIS packages that need to be able to move from Dev to QA to Staging to Prod. For the life of me I cannot get any of the packages to *READ* the config files created with the package config wizard. All I want to do is move the connection string out of the package so we can change the config file and not have to touch (hand edit) each package. Any help is appreciated!
I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsx Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsxWarning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsxAny ideas??? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!
Hello, I have a package and config files created, deployed ssis on server..Now I had to change something, so I saved again package(save all button used) and by click build/rebuild make my config files dissapear from original location, when I run manifest file, it looks for these files and complain they cannot be found.. So in order to get my files back what should I do..? P.S> I tried to recreate files, save all again and build or rebuild..Doesn't work..Any help..?
I need some clarification. I am trying to utilize the XML Direct Configuration in my SSIS packages to utilize database connections in the package. I am wanting to utilize this the same way you could use UDLs in the SQL Server 2000 DTS packages.
Currently, I am creating the dtsConfig file and saving it to my desktop. I then modify it with notepad and add the following configuration where it looks like this:
Once this is created, I am trying to "re-use" this in SSIS packages created moving forward, where they all point to this configuration for the same database connection.
What I don't understand is when "Enabling Package Configuration" and then pointing to this dtsConfig file doesn't create a connection in "Connection Manager" NOR does it provide a way to create a blank connection and point to this configuration.
I feel like I am missing something thats so "great" about XML configuration files. Any help would be appreciated.
I have several packages that share one configuration file located at a network location. Up until i installed sp2 everything worked fine, but now the packages "loose" the path to the configurationfile. It is replaced by some c:programsqlserverpackage.... path where no config file is located.
I can run packages if i specify the config file explicetly, but I really don't wanna do that, since it will not work in the context i'm working.
I am using the wizard to install my packages.
Anyone with similar problems ? Any help appriciated.
Note: This is not the issue that sp1 solved (for me at least). =)
For selecting different load targets we use package configurations to override the connection parameters in the connection manager. To organize the different tasks we splittetd our load processes into multiple package files (dtsx) which are then put together into a workflow using "Execute Package Task" component. With the option /CONFIGFILE its possible to override the configuration file for the package that is called with DTExec. Unfortuanetly it does not override the configuration of subpackages calles by the "Execute Package Task". Are there some ways to configure subpackages on the command line?
I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig (on my dev machine).
Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.
However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:....myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:projectsMyIntegrationServicesProjectmyConfigfile.dtsConfig cannot be found.
What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.
€™m just starting out with SSIS and am trying to establish architecture standards that are sound and workable. We will be developing BI solutions for customers who use our banking and finance package that runs on an IBM System i. Modularity is important as all our BI solutions will have a combination of re-used functionality along with varying degrees of unique functionality for each customer. My current area of interest is in externalizing all environmental factors and ensuring such things as passwords are protected.
I understand how configuration files work and how to use environment variables to provide a layer of indirection to them. At this stage we want to avoid encryption of packages and plan to use file system based deployment. We plan to use windows security for SQL server so I have no issue in exposing connection info here but for our DB2 connections we need to include user id and passwords. As I understand it, configuration files can not be encrypted so how do I store this externally while preserving its security. Nothing I have read so far has given me exactly what I need.
I nabbed a couple from some recent installs I did, and, assuming I can get my SAN guy to always give drives the same letter assignment, it looks fairly useful.
There's still a bunch of additional stuff to do, like turn on instant file initialization, size/create additional tempdb files, and set up alerts and database mail, etc.
Can the configuration file be expanded to handle any of that? Do you use something else to template/automate your SQL installs? The more I do it, the less I want to keep doing it.
I have a package in which I have enabled "Package Configuration".
When I run the package i am sure that it reads the configuration file and executes the package correctly.
However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.
I have event handlers for OnError and OnWarning and both these are NOT invoked.
IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).
Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?
