I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file. The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.
When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file). However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development). I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).
Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account. This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).
What is the problem here €“ surely changing the way in which the package runs should not affect the configuration file settings! Any help would be appreciated€¦
I cannot get the log file path read from the configuration.
If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?
Here is the portion of the config file. Everthing else is read from the config file correctly.
We are deploying our SSIS packages into different folders. For example: Test1 est2 production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
I'm sitting down again to see if I can get configuration files to work. To test, I've set up a simple package that reads a flat file and writes to a table.
I've enabled configurations by clicking the checkbox in the configurations window. I am using an xml file on the local server, and there is an absolute path defined to the file. I also added a password to the xml file so the connection will work. To this point, everything works.
However, when I go into the configuration file and edit it to point to another server, it doesn't pick it up!
That is, when I run my package, it still writes the data to the old server. It's like it's completely ignoring the configuration file and just using the OLE DB connection manager instead.
However, if I delete the OLE DB conn mgr, the package doesn't work.
That's the question. I'm sure I will be encourage to use xml or other configuration types rather than INI files. But INI file has the advantage of not embedding any object property path and can be used for both packages and other applications. Imagine a large implementation with hundreds of packages and dozens of class libraries and applications which are configured from the same INI file. Imagine also that the production environment must support both DTS 2000 and SSIS 2005 packages as it's impossible to migrate all at once.
I've read that SSIS would support INI configuration files:
In Package Configurations Kirk says:
"...The two types of package configurations that haven't been documented yet in books online is INI and SQL configurations. INI package configurations have basically been provided for backward compatibility..."
And in BOL, in the DTSConfigurationType Enumeration description we can find that the INIFile configuration Type is also mentioned.
Anyway, a possible workaround is building a custom task with an UI that allows to assign INI keys to object properties, but I wanted to know if it's possible to avoid this.
I created an SSIS package that is connecting to 2 SQL Databases, one connection is using windows auth and the other database connection is using SQL Auth to connect to the database. I setup a config file to use for my ID, servername, database, and password but when I run my SSIS package it fails stating'
"[Execute SQL Task] Error: Failed to acquire connection "dbConnection". Connection may not be configured correctly or you may not have the right permissions on this connection."
If I open up my connecting dbConnection in Connection Manager the username is filled it but the password is blank, if I enter in the password and click 'test connection' it passes. I have the ProtecionLevel = EncryptSensitiveWithUserKey but the password is still 'blanked out' So my question is
1) how can I use the config file to store my id and pwd for my db connection? 2) how can I get my package to read the config file to get the ID and password for my database. 3) is there another way to do this? Would a varaiable work for this, if so how do they work?
I have a package where I store connection managers in a configuration file (XML file). I updated manually the XML file to add the passwords.
When I launch the command line generated by dtexecui, everything is good: "dtexecui /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW "
When I launch the command line with dtexec: "dtexec /FILE "E:mssql90DTSPackagesBVOMDWRefreshStage Refresh EUR 1.dtsx" /CONFIGFILE "E:mssql90DTSPackagesBVOMDWRefreshEnv_Prod.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW ", I have the familiar error: "Failed to decrypt protected XML node "DTSassword""
I have the package level set to "EncryptSensitiveWithUserKey", but I think I don't need to update it to "EncryptSensitiveWithPassword" because it works with dtexecui.
I have sucessfully merged and recreated my 1st DTS package to SSIS. now I am working on the configuration file and deploying it. I have a question about config files. I have set one up to use with my package pointing to proudction data sources. If I wanted to to set one up for a development source do I just create another config file for development. How do I tell it then which config file to use when I am testing?
IE we only have one server but will have a production and a test db. Right now just have prodcution. We have just merged over from 2000 to 2005. Just wanted to make sure I understand how to point it to development when I want to to this. '
In my project, developers enable configuration on the packages they develop. This means that if a project has 100 packages, each one has its own configuration file.
If our primary database server changes, I have to go an update 100 configuration files.
This defeats the purpose of having a configuration file in the first place.
I want to have a configuration file which contains common settings for all the packages in the project.
At the same time, I should have the flexibility of having individual configuration files for the project.
I have a package for which I have set ProtectionLevel to DontSaveSenstive.
I connect to a SQL Database which supports mixed authentication.
To connect to this server in a flexible way, I have enabled SSIS package configuration.
I have edited my dtsconfig file with notepad and put the right password there (I am able to use management studio to connect to that database using same password).
Now when I come back to BIDS and double click on my connection manager, it doesn't read the configuration from the config file. (it just has a blank password). So it seems that BIDS doesn't read the config file.
What can I do to make BIDS read the config file.
Also, is there a way to encrypt the password (without keys) in the configuration file?
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?
When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.
The archive log looks like : SQL Trace ID 2 was started by login "sa"
Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.
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.
I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package. I get error that connection exist in the configuration but not in the package and "Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists." Any help?
Thanks to some earlier help from this forum, I have my package running using configuration files and Windows Authentication. However, when I try to execute the package using Sql authentication, login fails for the user i am specifying. When I check out the configuration file, the connection string does not contain the password. I added the password to the connection string, but it does not cone back. I followed the instructions found on the following site:
Is there any way I can configure the Configuration file Path.
I have an understanding that wherever I store my package but my Configuration file(XML) must be store in the place specified in the package. In my dev environment I have C Drive as such the package is in DevServerNameC:SSIS.
However, there is no C drive allocated in Prod envirnment as such package is going to rest in ProdServerName/SSIS
Now if I place my package and execute here and it will look for configuration files(XML) which are on C file path. Then I guess my package fails.
I am just wondering how can I make my package configure file path dynamic?
I have an SSIS package (which is already delivered to the customer, so I can't change it), but I want to switch off (disable) one of the elements. I use a dtsConfig file (XML), so I want to just send an update to that config file to switch off the element.
So, I used the Package Configurations Organizer to generate a new config file with the element switched off, and it generated the following:-
<Configuration ConfiguredType="Property" Path="PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]" ValueType="Boolean"> <ConfiguredValue>0</ConfiguredValue> </Configuration>
So I copied these lines into my "production" config file and ran the package, but got the following warning message:-
Warning: 0x80012017 at ImportData: The package path referenced an object that cannot be found: "PackageRun Data LoadForeach Data FileLoad Invalid Format Recs into rej_dat_format_errors.Properties[Disable]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
Can anyone explain why? Is there some issue with having spaces in the Path name perhaps? Is there some syntax to get around it? Why did it generate a Path that is wrong? Thanks - AA
I have developed an SSIS package which uses an XML configuration file for connection information. It runs fine both from Visual Studio and from an Agent job after I specify the location of the XML configuration file in the Agent job definition.
My question is regarding the target location of the XML configuration file after deployment. I do my SSIS development on a local workstation with SQL Server 2005 DE and Visual Studio 2005 installed on it. I deploy my SSIS packages to a remote SQL Server 2005 SE server. Both machines are running SP1. I double-click the package manifest from my local workstation and specifiy the remote server in the deployment utility. The package gets deployed to the remote server just fine but the XML configuration file ends up on my local workstations C:Program FilesMicrosoft SQL Server90DTSPackages path. I would have expected that the XML configuration file would have gone to the same path on the remote server, where the package itself was deployed. Obviously, when I run the deployment utility from the remote server, it stores the XML configuration file in the same path on the remote server. But this is not a workable production solution.
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.
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
I've been working on package configurations here, mainly trying to figure out what works, and how. If I use an XML configuration, and and don't specify the path, ie just typing in test.dtsconfig, it creates the config file on my desktop. When I execute the packge in BIDS, it seems to be looking in that location. If however, I execute the package manually on the filesystem, test.dtsconfig must be in the same directory as the package (which is what I would expect). Why does this work differently in BIDS. Also, if the package is deployed to a SQL server, where would the configuration file need to be in this case? Thanks for the help.
I'm using an XML file to configure my dataBase connection string. I try to deploy my package on a new server and it works perfectly.
Then I made a second package which also need a configuration for the dataBase connection string. (I made the connection with the connection manager inside packages). The configuration is the same that for my first package, so I thought to use the same configuration file.
I can use the same configuration file but the problem is when I try to generate a deployment for my solution. I got an error which tell me that the xxx.dtsConfig file already exist and can not be copy again.
When I made the configuration in the second package I said that I want to reuse the file ... and I thought that for the deployment SSIS would know that it has to include that file only once ...
I'm using connection managers for all the connections i have in my packages in one project. However, if i change from one environment to another, i have to go to each connection manager in each package just to set the connection.
is there a faster way to change them like a configuration file lookup or something?
Hm... I use SSIS packages to manipulate some data from one SQL server to another. I chosed "DontSaveSensitiveData" and I am using a XML configuration file.
The problem is I have to edit the file with notepad manually and add the password for my SQLconnections in it.
Is it possible to do this from the Business Intelligence Studio instead?
I tried to edit the connections and set and save password there but it won't save it to the XML configuration file.
Or perhaps it is intended to be edited in notepad and passwords set manually to it?
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?
We are using Sql server 2012. One of our production database has hight no of vlfs. We are planning to shrink the logfile to reduce the no of VLFs but the database is configured for logshipping.
What is the effect of logshipping when you shrink the logfile?
i want to create single xml configuration file which should include the details to configure all the files in all packages.
Usually as u know whenever we configure the package one xml config file is created where we can change the path name for file or database details. Like that we will create a xml config file for each package.
but i want the solution as on xml config file for multiple packages
