.dtsConfig ConfiguredValue Contains Environment Variable?
Mar 7, 2006
is this possible?
<Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String">
<ConfiguredValue>%enviroment variable%</ConfiguredValue>
</Configuration>
it would be really useful. it looks like the .dtsconfig file needs to be maintained on each install independently. This makes maintenance a nightmare. it would be a lot nicer if the .dtsconfig files were more like templates rather than hard coded values to specific system resources.
View 5 Replies
ADVERTISEMENT
Feb 15, 2008
Hi All!
I have a parent package that contains two children... The second child depends on the succes of the first child.
THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...
Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...
I tried to execute coth children as Out Of Proc as well as In Proc...The same
Would anybody have an idea how to resolve this problem?
Thanks in advance for any help!
Vladimir
View 5 Replies
View Related
Jul 17, 2006
hi,
can you show me how to get the value of an environment variable from a script task?
thanks!
View 3 Replies
View Related
Aug 9, 2007
Hi,
I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.
Here goes..
SSIS package1:
Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.
Environment variable:
Pckg_config = <location of config file which has testcon1 and testcon2>
I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2
Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig
Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.
SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.
Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.
Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.
Thanx,Tushar
View 4 Replies
View Related
Jun 28, 2007
I am doing SSIS package configuration using environment variable.
I have created a system environment variable that points to the dtsConfig file.
I opened the package and choosen the configuration type as environment variable and specified the environment variable
When I click the next button , it doesn't allow me to choose the configurable property.
Please suggest
View 1 Replies
View Related
Jul 17, 2006
We are using Package configuration with environment variables. The problem we are having that if we try to open project from other PC (PC 2) it gives the error:
Error 1 Error loading F0005.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. z:visual studio 2005projectssales data martextract to staging areaF0005.dtsx 1 1
We are using environment variable named DWConfig and have configured correct path in each PC. If we edit package configuration in PC 2 and go thru the same procedures without any amendments the errors is removed for that PC and if, again we OPEN that project in PC 1 it gives same error and if we go thru package configuration wizard again error is removed.
Can any one tell me is there any solution of that problem?
Note: Our project is saved on server (neither PC 1 nor PC 2)
regards,
Anas.
View 4 Replies
View Related
Dec 17, 2007
Okay - this one is driving me batty.
I have a package that uses an environment variable package configuration of value X for a connection string. I close BIDS. I change the value of the environment variable to value Y. I open BIDS and the package, and the value of my connection string is Y. I save my package with the new configuration. if I look at the dtsx file, I see connection string with value Y. All as expected.
I move the package to my server (I've tried Import package from SSMS, using the deployment manifest, and save copy as). On the server, the environment variable is set to value Y. If I run the package or export it; however, the value of my connection string is X!
Does anyone have any suggestions of things to try or some reason that this is not working?
Thanks,
Jessica
View 6 Replies
View Related
Oct 8, 2015
I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
View 3 Replies
View Related
Feb 19, 2008
Hello All -
Have you ever seen the error message below?
Description: The package path referenced an object that cannot be found: "Package.Variables[User::<variable_name>].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning Could not load package "<package_name>" because of error 0xC0010014.
Basically, I create a package variable under my User Namespace and this variable will tell what server the SSIS is running at. We first create a system variable locally and the SQL Server will have a variable with exactly the same name so that the server name will be evaluated through the package variable/package configurations when the SSIS is executing from a SQL Server job.
This way we do not hard code the server name... We always succeeded on doing that with DTS as well as SSIS packages but just now my package is running into this issue...
Since I did not change ANYTHING in the package, I am guessing this is not programming related and that something was changed in the server. However, the DBA was helpless over here and I have no clue of what this error means.
Any help would be appreciated.
Thanks, Gabriel.
View 14 Replies
View Related
Mar 19, 2008
We're attempting set the ConnectionString for our configuration database connection manager from an environment variable, but SSIS seems to ignore the environment variable value. Deployment process:
Create the Connection Manager
Create an Environment Variable type configuration with the Target Property: Package.Connections[acConfigDBManager].Properties[ConnectionString]
Build the package
Copy the package and the manifest from the project's inDeployment folder to a folder on the server
From the SSIS server's console, Import the package from the File System.
Run the package, after first inspecting the ConnectionString in the Connection Managers collection
In all cases, the ConnectionString variable for our configuration database is the value in the package at build time.
So far we've tried the following variations:
confirmed that the spelling and case of the environment variable is identical on the XP development computer and the Win2003 server.
restarted SQL Server and SSIS
rebooted the Win2003 server.
built the package with a blank ConnectionString value in the connection manager
re-imported the package, overwriting the old one
deleted the old package before reimporting
renamed the package and imported
run imports from SQL Management Studio from the server console
assign the connection string from an arbitrary system environment variable
assign the ServerName and use an expression to build ConnectionString
What now? Has anyone been able to set a Connection Manager property from an environment variable?
View 16 Replies
View Related
Jun 16, 2015
I've created a SSIS Package and it's connection is based on Environment Variable(please seeprocedure).
Now, I'm trying to create a job that calls this package and it seems that when you view Data Sources, it still pointing to the old server.
But when you open-up the package through BIDS in the same server, it's using the new reference that I have specified in the environment variable (please refer to the first image).
I came across this blog with the same issues as mine. He suggested to re-start the SSIS Service which I already did but nothing happens. I even re-started the SQL Agent but still no luck.
I'm not sure what else is missing except for re-starting the machine which is the last thing I want to do as this is PRODUCTION server.
View 7 Replies
View Related
Jul 28, 2008
I have a Master Package that executes a number a child packages.
In my SSIS Package Configuration:
1. I have an SSIS Configuation table that holds the connection string.
2. An XML Configuration File with a setting of configuration location stored in an enviornmental variable.
3. And finally, an Eveniornmental variable with the setting of ProjectFolderAbsolutePath value, where it is the full path of the project folder.
The project functions normally but everytime I open it I get the following error.
" Warning loading MasterPackage.dtsx: The configuration environment variable was not found. The environment variable was: "EnviorVariable". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid."
View 5 Replies
View Related
Aug 24, 2007
Dear all,
I have a problem with SSIS reading an environment variable after deploying the packages to a server. I explain.
I have an Parent Packages ETL_MAIN_PACKAGE.dtsx that reads the child packages from a record set and loops on it to execute them with the Execute Package Task task. The first child package to be executed is called DIM_PERIODIC.dtsx.
On my local machine, the Parent Package is configured to read its database connections from an XML file SSIS_configfile.config located on my C: drive. The path (C:SSIS_configfile.config) to this file is stored in the environment variable BI_ETL.
When I run the Parent Package inside SSIS only machine, the connections are read and the package executes perfectly. Now, I want to deploy the packages on our server.
I copied the XML configuration file to the server C drive, I created the same environment variable BI_ETL and set its value to C:SSIS_configfile.config and I rebooted the machine (in case).
The execution of the Parent package is managed by a stored procedure. I use xp_cmdshell command. The command line generated is :
cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_DeploymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"
This command generates an error telling that the Environment variable is not found and it throws this error:
Error : 2007-08-23 18:59:10.25
Code : 0x80019003
Sourse : The configuration environment variable was not found. The environment variable was: BI_ETL. This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid.
End Error
Error: 2007-08-23 18:59:10.25
Code: 0xC001401E
Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I run the package on the same server with a command line directly in a DOS window:
C:>cmd.exe /c dtexec /file "C:ETL_DeploymentETL_MAIN_PACKAGE.dtsx" /CHECKPOINTING OFF /MAXCONCURRENT " -1 " /SET Package.Variables["P_PACKAGE_PATH"].Value;"
C:ETL_Deployment" /SET Package.Variables["P_LOOKUP_PATH"].Value;"C:ETL_Deplo
ymentETL_LOGS" /SET Package.Variables["P_SCHOOL_CODE"].Value;"007"
I don't have anymore the error saying that the Environment variable is not found, but I still have the same second error :
Error: 2007-08-23 18:59:10.25
Code: 0xC001401E
Source: ETL_MAIN_PACKAGE Connection manager "Package Path Execute"
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I conclude that the environment variable is not read at all.
Does anybody have an idea on how to solve this problem ?
Many thanks.
Sami
View 10 Replies
View Related
Apr 11, 2008
I'm doing a simple ETL that reads a database table and dumps the content to a text file. The text file will be named Employee.txt. This file name will remain the same across my environments, but I may want to vary the directory location to where I want this file dumped.
So, I defined an environment variable called "DataTargetDir" in all my environments. Now, I want to utilize this variable in the "File name:" box within the Flat File Connection Manager Editor. How do I do this? I'm thinking I can write something like "%DataTargetDir%Employee.txt" in the "File name:" box, but it's not working.
Am I approaching this the right way?
View 9 Replies
View Related
Nov 16, 2005
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies
View Related
Mar 20, 2007
When adding a SSIS DTSX package solution to Sourcesafe the SSIS Package Configuration file (.DTSConfig) is not included. We are using sourcesafe version 6.0d and Visual Studio 2005 Team Edition for Developers.
View 4 Replies
View Related
Feb 16, 2006
Hello all,
I'm trying to easily change a value in 80 ssis configuration files from "localhost" to "myservername". I downloaded WinGrep to do this, but it's balking on some of the files, saying that they are binary files.
Does anyone know how to make the *.dtsconfig files NOT binary in SSIS?
Any suggestions GREATLY appreciated.
I won't go into the reason I have my servername in 80 places...
Andy
View 6 Replies
View Related
Jun 12, 2007
Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.
I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).
I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.
There is a way to do it?
I don't know if i explained the problem well.
Thanks in advance
View 6 Replies
View Related
Apr 28, 2008
I have an SSIS package with a small number of connections for various databases, both SQL2K5 and AS2K5.
When I build the package the .dtsConfig file is produced but the contents for some of those connections are not accurate relative to the package itself. There are some old values retained in the .dtsConfig file.
Worse, when I deploy the package to another machine the installation should allow me to change these values to ones appropriate for the new server. While I am able to change them they are not retained and the old values crop back up making the package fail during execution.
Any idea where I can smack this thing to unstick these values?
Would specifics help? I can post the file(s) if need be.
Thank you in advance,
Erik Klein
View 7 Replies
View Related
Oct 17, 2007
Apologies if this has been posted before and feel free to point me to an existing thread if it has.
I currently have a dev, stage and live environment for a package
i have created a load.dtsconfig file that contains various bits such as connection strings, email to, from, etc which i created via the package configurations in the dev package. I then made a few changes via XML Notepad
I went to use the same config file on stage and decided to add a further property. I selected package configurations on stage and selected edit it the package configurations box, added the property and clicked finish. However it reset all the existing properties to the values that exist in the actual package so all the changes i made via XML notepad were overwritten.
Is there anyway you can get the Package configurations editor to retain the values from the .dtsconfig file rather than reading in the values from the package???
cheers
Scott
View 4 Replies
View Related
Dec 11, 2007
hi, all;
i'd like to resolve this problem:
We are Developing packages in Dev.Server; if we are Deploying our packages to Prod.Server; we need to modify the connection string and related properties of the connection manager.
1. How to modify the connection manager while deploying to production server?
2. can we create 2 dtsconfig file for a single package; is there any mechanism for instructing sequel server to use a particular dtsconfig file for our package.
i modified the config file in order to change the connection string. but its affecting only the config file not the package. while running the package its reffering the old connection string only.
please provide me some help
Thanks
View 16 Replies
View Related
Jul 9, 2007
Hi,
Up to now, we're using SSIS variables for storing values such as server name, database or several paths.
All these variables are required when our developers give to us their DTSX packages for to be updated in production scenarios.
And now, we'd like to use xml files but I don't know how to. We've already got xml files for Data Flow, Sql Task and so on..
Thanks indeed for your time,
View 4 Replies
View Related
Dec 10, 2007
hi i want to know what are the purposes of dtsconfig file for the package.
once we deploy our package, if we want to modify the connection string for the new excel source or data source or destination, how to modify the connection details at client site, or in the server,
please help me to modify these connection details after or before deployment at server machine.
View 6 Replies
View Related
Apr 7, 2008
I am going thru the Integration Services Tutorial found in Books Online and I'm on Lesson 3. It has to do with creating a configuration file for the source file location. I followed the steps and now I need to open the .dtsConfig file and type in the folder location.
The exact direction says,
"
In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task.
Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes.
Save the change, and then close the text editor.
"
When I open the file, I see this. What am I supposed to do here?
<?xml version="1.0" ?>
- <DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="FFTWayi" GeneratedFromPackageName="Lesson3" GeneratedFromPackageID="{74BC5CCC-5C51-4ABD-9263-AD3C000E9E1C}" GeneratedDate="4/7/2008 11:25:53 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String">
<ConfiguredValue />
</Configuration>
</DTSConfiguration>
View 13 Replies
View Related
Mar 13, 2007
Hi frnds,
im running the SSIS package " data" in XXXdatabase.
now i have to run the same package in different database and in different server.
so i mentioned all the database config details in sql_server.dtsconfig file.
here i had attached my file :-
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[YYY ].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=YYY;User ID=XXX;Password=XXX;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
after i attached the file in package configurations .
i was unable to see the db connection in connection managers.
i tried to attach the .dtsconfig file in (New OLEDB connection manager) but after clicking the test connection " Testconnection failed because of an error in intializing provider.The connectionString property is not intialized"
Please help me in this.
Thnkx in advance.
View 5 Replies
View Related
May 8, 2008
I was requested to build and move and existing SSIS package from the developers local PC to the Dev server. Both are running SQL 2K5, I built the following command file so SQL Agent can run the import job.
REM *** Run for FileImport
set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig
"C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigFileImportFileImportLoadData.dtsx"
All files are in the proper place but when the jobs runs the following message shows up in the history;
Message
Executed as user: LIGHTHOUSE1SQLEXEC. ...system32>set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig C:WINDOWSsystem32>"C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigDEVFileImportLoadData.dtsx"Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:54:00 AM Warning: 2008-05-08 08:54:01.77 Code: 0x80012014 Source: FileImportLoadData Description: The configuration file "FileImportLoadData.dtsConfig" cannot be found. Check the directory and file name. End Warning Warning: 2008-05-08 08:54:01.77 Code: 0x80012059 Source: FileImportLoadData Description: 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. End Warning Progress: 2008-05-08 08:54:01.82 Source... Process Exit Code 0. The step succeeded.
My questions is how do I fix this and is this really and error?
Jay B.
View 2 Replies
View Related
Dec 27, 2007
I use an XML configuration file for my SSIS package, and I have "Enable package configurations" checked. When I build the package, however, there is no dtsConfig file in the output (set to in) or the Deployment folder, and the config file is not included in the SSISDeploymentManifest file. There are no build errors or warnings.
This was working fine the last time I worked on this package (~9 months ago), but not now. I have since installed SP2, but can't confirm if the problem is related to a "fix" in SP2.
View 2 Replies
View Related
Apr 24, 2007
When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.
Thank you,
Mike
View 6 Replies
View Related
Dec 1, 2007
I built my package on my development machine with a config file at a given location. Let's say:-
C:DevelopmentdtsConfig.xml
Then I shipped my package to the Production machine and ran my package (in a .bat file) as follows:-
DTExec /FILE "C:ProductionPackage.dtsx" /CONFIGFILE "C:ProductiondtsConfig.xml"
But I get the following output:-
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:01:27 PM
Warning: 2007-11-30 22:01:29.52
Code: 0x80012012
Source: Package
Description: The configuration file name "C:DevelopmentdtsConfig.xml" is not valid. Check the configuration file name.
End Warning
...
But then it goes on to read values from my Production config file.
How can I stop the annoying warning about my Development config file-path (which doesn't exist on the Production machine)
Thanks
View 6 Replies
View Related
Nov 29, 2007
I am migrating from local to Dev,QA and Prod.
I created a .dtsconfig file containing database connection strings to Dev database. What is the "location" on the Dev server where this .dtsconfig file nees to be deployed to??
Thanks for your help.
View 5 Replies
View Related
Feb 16, 2008
Hello,
I have setup a package configuration and it ran fine; however, I would like to be able to use the script task to change content of the dtsConfig file. For example, I can get the newServerName using the SQL Task, then how can I assign the newServerName value to the DataSource in the package configuration file? Is this possible?
Thanks,
Andrew
View 17 Replies
View Related
Jul 20, 2015
I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig. I want to edit the deployed .dtsConfig but it won't allow me and always complained that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?
View 4 Replies
View Related
Nov 24, 2015
I have a job/step that call a Fileprocessor.dtsx package with 4 different config files. I need to make a enhancement to the job to call the same package using a new (parent) package with multiple iterations based on some business condition.
I am going to modify the job/step to call ParentFileprocessor now with same all 4 config files which in-turn have a call to run Fileprocessor.dtsx; my problem is, how do I pass all 4 config files for child call? Using which task?
I m not allowed to modify Fileproessor.dtsx (to be child pkg) SQL2012, MSDB package deployment model.
View 3 Replies
View Related
