How To Use Deployment Utility Using A Single Package Configuration File
Jan 22, 2007
Hi,
We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists.
Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file.
I am getting following error when "CreateDeploymentUtility" is set to true and I try building the solution. It tries to copy a file that already exits in inDeployment folder. I am using Sept. CTP.
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
In reference to the question raised in this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1460591&SiteID=1
Since I'm not able to create a deployment utility, when a config file is shared among multiple packages and also I cannot get the permission from Sys Admins to use Env. Variables I'm struck.
Now I'm thinking of importing the package to Sql Server from the file system. Is there any caveats in this approach? especially regarding the config files?
[edit] Also, do I need any special permissions to view the Integeration Services node in Management Studio? We are using Integerated Authentication, neither do I'm able to run sp_start_job sp in the msdb database. [/edit]
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."
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.
I've been searching for an answer to my question quite some time now and I've not been able to figure it out yet.
Situation: - I've created a SSIS package containing a bulk insert task. - I've added a package configuration containing the appropriate connection manager (i.e. dev, beta or live) - CreateDeploymentUtility = true - I've copied the deployment folder to our beta server and I started the manifest file to install the package to the sql 2005 server, after that I specified the config file location and changed the value so the approriate connection manager is used. - When I execute the package from the sql server the package doesn't read the value from the xml config file, it uses the connection which was originally specified in the package, whereas when I run the package from my BIDS it is reading the value from the xml config file?
I can't seem to figure out why this is happening? am I missing something here?
We deployed the ssis package to SQL server and now trying to configure but it only allow use to change environmental variables there is no option to browse and select XML configuration file. Does this mean when you  are using SQL server deployment mode u can only use environmental variable ?
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.
However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.
When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.
How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?
Trying to build a deployment package. I have a number of dtsx in a project that share a connection config file. When I build, the error states: 'Could not copy file "whatever.dtsconfig" to the deployment utility output directory. ... The file already exists'
My parent package calls packages stored in the file system. While developing, I would like to call packages in the project bin directory. In production, I would like to call packages in a different development. Is this possible?
I can change the package connection string with an expression that refers to user variables PackageLocation1 or PackageLocation2. I would like to do this automatically. Is this something that should be done at deployment time? Or is there a run time value that I can check and conditionally use PackageLocation1 or PackageLocation2?
Development and deployment is done on the same server, so the same enivronment variable value would be used in an indirect configuration. Same thing applies to a file configuration.
Another question: Is it possible to set up a different Installation Folder for use during deployment? Every time I deploy, I have to navigate the folders, you can't even paste in the folder name.
I am getting the following error when trying to run a deployment utility. The server only has .net framework 2.0 and sql server 2005 (rtm) installed.
If it's any help, this server was recently upgraded from the june ctp to rtm.
This is a production server, so a solution involving as little disturbance to it's current state would be preferrable.
Thanks in advance for any suggestions.
-Jeremy
TITLE: Package Installation Wizard
------------------------------
Unexpected error occurred.
------------------------------
ADDITIONAL INFORMATION:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
------------------------------
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154. (Microsoft.SqlServer.ManagedDTS)
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@Error int,
@ExecCommand varchar(512),
@FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @FullFilePath = 'D:DocumentationProjectsIntegration ServicesBIDS ProjectsTesco DNF Integration ServicesTescoDNF ProductPromoSQL CodeOBJECTSCreate DB TescoDNF_SSISPackageManager.sql'
SET @ExecCommand = 'xp_cmdshell ''sqlcmd -S RgalbraithSQL2005_1 -i "'+@FullFilePath+'"'' '
I would like to have my developers responsible for deploying their SSIS packages to the Test/QA environment. I tried granting access to several of the stored procedures in msdb and the sysdtspackages90 table. The only thing that seems to work is granting sysadmin priviliges. Is there a server or database role that will grant the appropriate access? thanks
I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.
The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.
How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?
I'm having difficulty configuring a package to look for an xml configuration file that contains values for source/destination connection strings.
The goal is to be able to have a package and its configuration file on one file system and to be able to copy/move the file dtsx and dtsx.config between build/staging/production servers while only having to set the xml value for the source/destination configurations.
In the package, I've created an xml configuration file called zzz.config. I took the absolute path out of the location of the xml file so that I have xml location = zzz.config instead of xml location = e:ssiszzz.config I'm assuming that when it gets to the point of validating the location of that file, it starts by looking in the current directory where the package is found.
All is well as long as I execute the package through the execute package utility by double clicking on the package file.
The problem lies when configuring a job in the sql server agent, it doesn't seem to pick up the xml configuration values because its pointing to the old server and not the current server stored in the xml file. I'm assuming its because the current directory is not the package directory at this point.
How can I get around this? Do I have to move the xml config file (zzz.config) to another directory where the agent will look for?
I know I could configure this as an environment variable but this contradicts the point of having movable packages without any extra tweaks.
The system creates a XML file but when I run the package I get the following error in the output pane. Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:ProjectsVinciSSISDVLFMC loader ImportFMC Loader ImportFMC Loader ImportJACBE_IF_CONFIG.xml". SSIS package "FMC_People.dtsx" starting. Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009. Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation. SSIS package "FMC_People.dtsx" finished: Failure.
I don't get it. Where do I go wrong?
I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.
does anybody know where is stored information about belonging a certain configuration file to a package? How can I connect a configuration file with an installed package without using SSISDeployment wizard?
When I create a deployment utility I... - set AllowConfigurationChanges to True. - set CreateDeploymentUtility to True - DeploymentOutputPath is set to binDeployment - I click 'Build' and it creates the binDeployment directory and writes all my DTSX files and my dtsConfig file But it doesn't create a manifest file. Why not? Thanks
I created a simple file system task to copy a file from one location to another on my local system.
I used variables to specify the source and destination paths and now in my configuration file if I configure the values to something else also the package is taking the initial default values and not the values specified in the configuration file.
So I've seen articles outlining how to execute a package in VB and how to pass variables along to the package, which is great.
But I'm wanting write a VB.NET app that will read the xml in a .dtsx package, looking for the available variables (and datatypes) that need to be passed to the package to configure it properly. Anyone done this? Is it best to use parent package variables? Or another type of configuration?
When looking at the xml of a .dtsx file, I can see a variables section that could be parsed... is there anything letting me know what is required versus optional? How do I know the datatype that I need to pass? Any help would be appreciated...
Started: 10:48:56 AM Info: 2008-01-24 10:49:02.92 Code: 0x40016041 Source: SsisEndBatch Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc ENDBATCH TEST ENV.dtsConfig". End Info Warning: 2008-01-24 10:49:03.15 Code: 0x80012011 Source: SsisEndBatch Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid. End Warning
We are facing some issues with SSIS and thought if you could be of some help.
Actually, instead of Deploying our SSIS projects we copy the SSIS files to a different server from where we execute the SSIS Packages. The reason being, we have a Core Version of the application and this is deployed to different markets and subsequent customisations are performed in the market specific version of the Packages. The problem is although we have a Package Configuration Xml File for our Connection object, the first time when we copy the Packages, we have to manually open each of the Package and change the URL of the Xml File for Configuration. I read through some of the articles on the internet and came to know that the Package Configuration does not support relative path and only absolute path is possible.
Can you please share some of your suggestions on the above problem.
I've added an XML package configuration file to my SSIS package and it appears to be working fine.
The XML file is located in the same folder as the solution file.
I've recently added the entire solution to Visual Sourcesafe 2005, and I achieved this by doing the following:
1. Right-clicked on the Project name in the solution explorer and selected "Add to source Control". 2. When prompted, provided my Sourcesafe login credentials and clicked "OK" 3. Accepted the default VSS project name and clicked "OK" 4. Clicked "Yes" to allow VSS to create a new project
Problem: The XML configuration file hasn't be added to sourcesafe along with rest of the solution.
I'm able to add the file manually myself, but this doesn't seem right to me. Am I missing something? Should the config file show as part of the Solution in Visual Studio 2005?
I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.
For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.
I've used XML package configuration in my packages in order to populate key variables. The configuration String is pointing to a local folder in my machine. After that, I've checked my whole solution into TFS.  I did check the checked in file but could not find the .dtsConfig XML file. The problem occurs when the other teammate checked out this solution from TFS into his own box. When he tried to open the solution, it gives warning (not error though) saying it could not find the package configuration file.machine does not have the same URL I had in my  box.  In situation like this, how can we fix in the multi-developers SSIS environment?
Could someone please explain to me the client configuration utility in plain English please.
I have VB applications installed on the sales force`s laptops that log onto SQL Server through RAS and an ODBC connection. The client configuration utility is not installed on their machines, but everything works fine. The BOL say that when installing utilities, the user is given the option of selecting the net-library to use. I am not, when installing it on my NT Workstation, or W95 Operating system. The BOL also state that the control panel has an entry to allow selecting which protocol to use. I cannot see it
Who needs the client configuration utility installed, obviously not the clients, is it just the people who want enterprise manager on their desktops ?
I am completely confused by this application This is the one area of SQL Server where I am lost, help !
I have been reading through different posts about how to manage SSIS configuration files across multiple environments. I have seen a few that mention a solution that turns out not to really work (or maybe I€™m doing something wrong); which is what I hope to find out here.
Solutions I am working with: 1: Put the XML Configuration file in the same directory as the SSIS package and only specify the config file name when setting it up in the package. I do not specify a path to the XML Configuration file, just the config file name.
ISSUE: This works when I first create the package and test it without closing down visual studio. Even after deploying the SSIS package and the config file and moving it between environments it works as long as the working directory of the DTExec command is the same directory as the SSIS package and configuration files. The problem comes in after I have closed Visual Studio and opened it again and loaded up the SSIS package. It then gives a bunch of errors stating that it can€™t locate the configuration files€¦. If I could fix this issue, then this would be a solution that I could live with. (I would much rather use a relative path to a different directory but I€™ll live with what I can get). Can anyone solve the issue of Visual Studio 2005 not recognizing the location of the configuration files when only the configuration file name, and not the path is specified?
-- The other odd thing is that I have been able to get this to work consistently with some of my packages but not others. I'm not sure what I did to get those packages to recognize the location of the xml config file.
I have a SSIS package on my local machine, and would like to deploy it to DEV server. Which files should i be moving to the SQL DEV Server? and where? How do I modify connection managers or is there a way to do that from the Management Studio or some other way? Thanks and I would definitely appreciate some prompt advise.
I've been doing some VB6 app development against a SqlExpress database, but have confusion with issues of deployment and configuration. It's my intention to distribute the app and db to client installations, both as stand-alone and network-ready shared access. Here's basically what's occurred thus far:
The database originated as an Access mdb file. Downloaded and installed SqlExpress, Tools, Manager, and sample database(s). Used the Upsizing Wizard in Access to create a SqlExpress db. Attached to the database in Express Manager so I could examine and access the table data. Hit the data through the application with the following connection string:
Now, some points of my confusion thus far with regard to deployment:
1. Named Instance and User Instance. I assume I'm using a Named instance with the preceding connection string. Honestly, I don't recall doing any kind of adminstative setup/configuration of the db except attaching to it in Express Manager, so I'm confused over why I would need a User Instance for a local copy of the database. Are there setup steps I've missed or did the Upsizing Wizard do them for me? I've read the white paper on user instance usage, but it raises as many questions to me as it answers. I do understand User instance is intended for local copy of the db, even though I'm not using it that way in development.
2. Do I distribute both the MDF and LDF files? I saw mention somewhere not to distribute the LDF log file, but the app doesn't work without it being there.
3. What has to be done for deployment to a server for shared acccess. Aside from installing SqlExpress, and possibly Manager, I'll need to configure for remote connection usage using Configuration Manager or Surface Area Configuation tools. Does this need an administrator to create and establish users accounts, rights, and such?
4. Are there any reference books out on this stuff yet, besides books online, which I don't find satisfactory.
5. Can SqlExpress coexist on Sql 2000 server? I realize SqlExpress is essentially Sql 2005, but our existing server is Sql 2000. Should it go on a different server?
Sorry for the myriad of newbie questions, but I'm a programmer and not a db admin.
