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
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
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.
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
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.
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."
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
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?
How can I specify the target object when trying to set a parent variable package configuration programatically?
I am trying to set the Target Object to be "ParentLogID" and the Target Property to be it's "Value". I think this is how I'd add the configuration and set the source.
'Add a Configuration
Dim config1 As Configuration
config1 = package.Configurations.Add()
config1.Description = "Set ParentLogID"
config1.ConfigurationType = DTSConfigurationType.ParentVariable
config1.ConfigurationString = "User::LogID"
Hello
I am trying to use Foreach loop container - Foreach File Enumerator
Is there easy way to retrieve enumerator configuration folder from variable - so I can easily move package from server to server?
I would like to have name of the folder we getting files from retrieved from variable by using this control
Thank you in advance
Armine Bell
I have a production server log shipping to a secondary server every 30 minutes (both SQL 2000), which the second server is used for both a warm standby server and for reporting from users. Issue: the log shipping locks the DB so reporting can't be done until the load is finished, the load to the second set of databases has taken up to 15 minutes to finish allowing the users only 15 minutes to run reports, this is not acceptable. The server also needs to be used for DR.
I am looking for another solution, I can't use Transactional Log shipping as not all of the tables in the databases have a primary key identified. So, I am looking for a real-time or near real-time reporting server that is more available to running reports and a warm standby server for Disaster recovery. I am trying to figure out what SQL Server 2000 has to provide (or even 2005 or 2008?) or I am also looking at some third party software, but not sure what is the best for a reasonable price.
Any help is appreciated.
Thanks....JB
Hi,
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?
cherriesh
Hi,
Please let me know if the following server configuration is possible?
SQL Server Database Services on machine #1
Analysis Services and Reporting Services 2005 on machine #2 (without SQL Server Database Services)
Post installation, need to setup Reporting services configuration. In that, within Database Setup, point to database installed on machine #1.
I am facing problem in pointing to remote database.
Awaiting some reply.
Thanks!
I'm using Virtual PC on my WinXP PC, with two W2K3 environments runnings as Virtual PC guests. My goal is to use these virtual servers to emulate a distributed SSRS environment--prior to setting up a similar environment on physical servers.
One of the W2K3 guests is my database server; the other W2K3 guest is my report server. Just like with a physical SSRS environment, there are many choices that need to be made during the installation and configuration--any one of which, it seems, can prevent the environment from working properly.
Currently, I'm struggling with getting my virtual distributed SSRS environment to work. I could go into the details, but I figured I'd ask a basic question first: Is anyone aware of a book, magazine article, blog, etc. that goes through a distributed SSRS installation--listing the requirements, discussing the choices, considering the implications, etc.?
Off the top of my head, such a walk-through would ideally cover networking configuration issues (Virtual PC or otherwise), Active Directory considerations, Windows Firewall settings between the servers, SSRS-related service account selections, authentication between the servers, etc.
If no one has produced such a walk-through (!), I'd be happy to write one so that others don't have to deal with the same struggles I'm currently dealing with....but I'd need your help.
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
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx
Wizard results:
Name:
ETL
Type:
SQL Server
Connection name:
ETLConfiguration
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.
Configuration table name:
[dbo].[SSIS Configurations]
Configuration filter:
PT_CUST_ABR
Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]
USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Hello--
Is it possible to install/configure SQL-Server 2005 on a multi-processor machine so that the relational DB utilizes a given subset of processors while Analysis Services utilizes another subset?
Thanks,
- Paul
hi,
can you show me how to get the value of an environment variable from a script task?
thanks!
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.
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 RelatedI'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.
Hi,
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.
Is this possible?
Thanks for your help.
$wapnil
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
Hi guys,
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?
Hope someone could help. Thanks.
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?
Hi, All,
I am using Package Configuration to simplify SSIS package deployment process. All the configuration information are stored in XML file. So far so good, However, since I have many, 20, packages. For each package, there is one configuration file to it. During the deployment process, I have dynamically modify connecting string (server name, DB name) to new ones. It ends up 20 or more modification and it's eaily for me to make mistake. Is there any workaround such as setting up environment variable, I guess, to allow me only modify once and apply it to all the packages?
TIA,
John
hi all!
This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.
when i try to execute through cmd prompt by setting conn <new database connection string> it fails.
Is package configuration is the only solution. how can i change conn string depending on different server?
Any help would be appreciated.
Thanks,
Jas
This has only started happening in the last two days
When we invoke our SSIS package from a web service it used to work fine but now gives the following error. the DLL name changes all the time. Any help would be appreciated
Code SnippetSystem.IO.FileNotFoundException: Could not find file 'C:WINDOWSTEMPvrjeaanf.dll'.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] sources)
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Evidence evidence)
at System.Web.Services.Protocols.XmlReturn.GetInitializers(LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.XmlReturnWriter.GetInitializers(LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.MimeFormatter.GetInitializers(Type type, LogicalMethodInfo[] methodInfos)
at System.Web.Services.Protocols.HttpServerType..ctor(Type type)
at System.Web.Services.Protocols.HttpServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest request, HttpResponse response, Boolean& abortProcessing)
Dear all,
I have an SSIS package and want to execute it on a computer that only
has SQL Express installed on it. Since SQL Express didn't brought
dtexec or dtui so what should I do ?
Sorry for my bad English...
Thanks for advance,
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have deployed a project with multiple packages to SSIS 2012 db. I am able to configure the project parameters fine. But, I am not able to replace the package variable values with the 'Environment' variables.
View 3 Replies View RelatedHi Guys,
Here's the current scenario:
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
Thanks,
kervy
Hi friends,
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
Thanks
Subhash Subramanyam