XML Configuration Files -- BIDS Vs. Dtexec
Jun 23, 2005Hoping someone can help me out quickly on this.
View 9 Replies
ADVERTISEMENT
Package Runs Fine From BIDS But Dtexec On Same Box Gives Insufficient Product Level Error
Mar 16, 2006Running eval. edition of Sql Server Standard 2005. "Insufficient product level" error is thrown during validation phase of an OleDBCommand data flow task. This task type is not licensed in Sql Server 2005 standard? The component runs a very simple sql update statement against a one row table in Sql Server 2005.
If it works from BIDS, should it not work from dtexec.exe on the same box?
Does dtexec run under the security context of the logged in user?
Thanks,
Ken
Dtexec Vs Configuration File
Oct 9, 2007Hello,
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 don't understand... What I should do?
Thanks,
Guillaume
Configuration File Location Different In BIDS?
Jul 6, 2006I'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.
View 1 Replies View RelatedRun SSIS Package With Alternate Configuration Via DTEXEC?
Jan 31, 2008It looks like you can run an SSIS package via the DTEXEC command. You can also specifiy a configuraiton file flag on the command. Is there anyway to specify a configuraiton filter if you stored it into a table vs and xml file?
View 1 Replies View RelatedSSIS, BIDS And Temporary Files
Feb 1, 2007Why are some SSIS files, generated by the Import/Export Data wizard put into the local users temp folder? Why are these not compiled with the package when the solution is built?
Is there some setting I am missing?
This architecture is kind of silly, as the server always needs access to the temp folder on the local machine to run.
How can I get these temp files packaged with the rest of the package and deployed to the server so the server can run independent of the machine I develop the package on?
Thanks,
Jeff
Visual Studio/BIDS Out Of Memory Errors Opening Large DTSX Files
Jul 26, 2006I'm currently experiencing major problems with SSIS when opening and editing large .DTSX package files that contain Exec DTS 2000 Tasks which have the package data loaded internally. I have no issues if I point the task to a .DTS file, or to an actual DTS package on a SQL 2000 server - but if I load the package internally then once the underlying .DTSX file gets over around 17MB or so in size (which doesnt take long making a few edits to even fairly simple packages now and then), I start to experience major issues with VS/BIDS 2005 crashing randomly when I try to perform any action with the package (open, save etc). Things like OutOfMemory exception errors, followed by the properties of Exec DTS 2000 task being deleted, and also sometimes accompanied by messages about the application not being installed properly.
Again its ONLY when the underlying .DTSX file reaches a certain size limit, and only when I've got an Exec DTS 2000 task with the package loaded internally. I've replicated the issue using several different package files on several different machines (even on servers with lots of memory, fwiw).
Can anyone out there help me with this? SSIS - namely SSIS Exec DTS 2000 package tasks - are our lifeblood at my company and this trend of random and serious crashing on large package files is very disturbing to say the least.
thanks,
Wil
Problem: SSIS Package Failure Using 32-bit DTExec And 64-bit DTExec
Apr 17, 2008Hi all,
I have a serious problem with my SSIS Package while executing using 32-bit DTExec and 64-bit DTExec.
Here are the details:
Environment:
Windows Server 2003 64-bit (Build 3790: Service Pack 2)
SSIS 32-bit & 64-bit installed
SQL Server 2005 (Microsoft SQL Server 2005 - 9.00.1399.06 (X64) - RTM)
SSIS Package details (compiled in 64 bit)
Script tasks only
Microsoft Visual Basic .NET (using TRY...CATCH block)
PreCompileScriptIntoBinaryCode = TRUE
Run64BitRunTime = TRUE
Execution
Batch file that uses DTExec to execute the Package.
SCENARIO
I am trying to exeucte the above SSIS package using both 32-bit and 64-bit DTExec to make it failure by providing invalid connection string. Here are the details,
Wrong connection String using 32-bit Execution
While establishing the connection the error message has been nicely captured in to my Exception block and writes into the log file.
Wrong connection String using 64-bit Execution
While establishing the connection the error has not been catpured anywhere (although I have TRY CATCH block) and it haults there itself with the message "Process is terminated due to StackOverflowException". Later I found that the error is due to the connection string along with the unhandled exception.
Please suggest any one of the following my findings, also if you have any other advice would be very much appreciated.
1. Shall I go ahead and fix the issue by handling those unhandled errors? (e.g Appdomain, application). I tried several but still not working using 64-bit DTExec.
2. Shall I go ahead and use 32-bit DTExec to execute the package? If so, is there any other major issue...like performance or anyother bug?
P.S: We cannot apply any service pack for SQL Server 2005 at the moment. Sorry abt it. If you have any specific hotfix for DTExec (without affecting SQL Server) then we can decide.
Sorry for the lengthy one and Thanks very much for you help in advance .
Thanks again!
Suresh
Configuration Files, Again
Jan 17, 2008Hi,
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?
Thanks
Using Configuration Files
Jun 20, 2006Hi 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»
Anyone knows why?
thanks,
VĂtor
Configuration Files
Jun 19, 2006Hi,
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
Thanks,
VĂtor Ferreira
Deploying With Configuration Files
Feb 15, 2008I 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?
View 4 Replies View RelatedConfiguration Files And Importing
Feb 28, 2006I 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.
Thx.
R-
SSIS Configuration Files
Jul 17, 2007Hello,
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?
Please, please help!
Mgale1
Configuration Files And Variables
Feb 22, 2006Hi ..
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
Sample Configuration Files
Apr 5, 2006I 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
Path To Configuration Files
Jan 22, 2007Can 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?
Thanks
How To Use Multiple Configuration Files?
Dec 14, 2006Hi everyone,
How to change dynamically the connection properties for a DTSX?
Imagine that you launch a SSIS and under a several rules the same connection is reused with four or five different databases.
I know that I can attach a configuration file or more than one but how to tell to SSIS which use in every moment?
Thanks in advance and regards,
Jobs And Configuration Files
Dec 26, 2007Please 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!
Thanks much!
SSIS Configuration Files - How Do You Use The Things?
Oct 16, 2006http://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!
View 13 Replies View RelatedPackage Loses Configuration Files!
Oct 25, 2006I 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!!!
View 2 Replies View RelatedHow To Access Package Configuration Files
Mar 11, 2008HI
how to access package configuration files of a particular package during runtime.
Do You Use The Reporting Services Configuration Files And If So, How?
Jan 15, 2007Wondering if I could get some feedback from 'you All' on how you use the Reporting Services configuration files.
Specifically, I'm interested in -
1) Have you ever made changes to the configuration files? (Yes/No; if Yes - how often?)
2) What if any operations you commonly do in the files? (list)
3) Do you script or otherwise have automated solutions for updating these files? (Yes/No; if Yes - in what scenarios; if No - did you try to?)
Any other feedback about the configuration files is also welcome.
Thanks,
-Lukasz
Problem With Configuration Files And Deploying
Jan 11, 2007I 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.
View 2 Replies View RelatedConfiguration Files Are Gone After I Change My Package..
Nov 7, 2007Hello,
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..?
Question: How To Run With Mutliple Configuration Files.
Jul 16, 2007Hello,
Here is the scenario.
I'm using SSIS and SQL 2005
I have two dtsx packages. Lets call them TESTA.dtsx and TESTB.dtsx. I've enabled using XML package configurations on both.
TestB.dtsx pulls some data from a table and writes it to a flat file.
TestA.dtsx calls TestB.dtsx.
For the test I changed the TestB fileoutput connection string to save to a different location.
When I run TestA.DTSX using DTEXEC. TestB's configuration file is not being used.
How do I kick off TestA.dtsx and specify a package configuration for test b?
I tried manually copying the Config parameters from TESTB's config file to TESTA.
When I run TESTB directly from DTEXEC and pass in it's config file as an argument it runs fine.
XML Direct Configuration Files For SSIS Packages
Nov 16, 2006Hello,
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:
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[ConnectionName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=[ServerName];Initial Catalog=[DBName];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
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.
Thanks,
Daniel Lackey
MCSD
SSIS Shared Configuration Files -SP2 Problem
Feb 21, 2007Hello,
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). =)
Execute Package Task And Configuration Files
Nov 25, 2006Hi all
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?
Thomy
Configuration Files Do Not Work In CmdExec Mode
Oct 18, 2006I 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.
Is there a way around this?
Secure Connection Info In Configuration Files
Jan 11, 2007Hi All
€™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.
SQL 2012 :: How Useful Are Saved Configuration INI Files For Automating Installs
Jul 17, 2014I 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.
No Error Thrown By SSIS On Missing Configuration Files
May 17, 2007Hello All,
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?