When I try the option to store package configuration into a SQL Server table, it keeps asking me for a filter and I dont' see any options on the drop down list. How do I set this up? I know how to set the package configuration in an XML file and that worked pretty OK for me. But there is now a need to store the package configuration in a SQL Server table now. Please help me with this problem.
View 1 RepliesHi --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,
If I have a package configured to use Package Configurations, and the Package Configuration is SQL Server, will the package still run if the database containing the configuration table becomes unavailable (ie SQL Server Service stops)?
Thank you for your help!
cdun2
Scenario/Requirements:
We have different SQL servers for each of our environments - for arguments sake let's just stick with Dev, Test and Prod (though in reality there are many more) and we want to isolate the environments - i.e. Dev, Test and definitely Prod should be able to stand-alone.
We want to be able to design a package once and then just migrate these through each environment and just reconfigure it externally to operate within the environment (so it made sense to use package configuration for this).Package configuration shall load things like server connection strings for that environment, and other variables specific to that environment with the intention that any package can use the same configuration for the target environment (and of course, allowing ease of migration).
The reason why we chose SQL Server to store the package configuration is because there are sensitive information used in our queries required to be stored and loaded between each environment and by using SQL Server we can manage all the security in one place (XML would require filesystem security which is kind of out of our DBA hands, as would other approaches). We would setup the configuration table to sit in the same place per server much like how the XML alternative would have worked.
Aim and Problem:
Be able to change the SQL Server where the package configuration
is loaded from after the package has been designed (without it becoming
a maintenance nightmare).However, it seems that DTEXEC
will always load the package configuration before any parameters via
the command-line are passed into the package (thus we can't change
where it looks for the package configuration).
Options explored and discounted:
Initially (before we discovered the issue with the order which package configuration was loaded), we thought we could just specify the connection string for the package configuration from the command line and it would thus load the appropriate variables from that connection.
Instead we found that by the time the correct connection string from the command-line was put in place, the package configuration has already been loaded and thus ignores it.
Indirect Configuration (i.e. via environment var) - for SQL Server configuration this allows you to vary the connection object (not the connection string), the table and the configuration filter.The problem with this is that if we chose to vary the later two, then once the package is migrated to Prod it would load the package configuration from the original connection specified in the package (i.e. Dev since that's where the package is originally designed to work in), which we can't accept this since Prod should be able to work without connecting to any other server.If we vary the connection object, then we need to define a connection object to each different server and that defeats the purpose of the package not knowing which environment it's supposed to operate in till run-time (and this would break if we add new environments or change the server that hosts that environment).
Any ideas anyone?
All we can come up with now is wait for MS add a new option for the SSIS package to allow the package configuration to be loaded after the command-line parameters are passed into the package (or provide some kind of SSIS task that reloads the package configuration we could add into the design of the package before it arrives at operations that rely on our environment specific settings).Thanks in advanced,Eric.
I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.
When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.
The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:
Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)
Could not complete wizard actions.
I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?
Many thanks
Matt
Hello all,
I have an issue with my SSIS package not using the package configuration specified in my SQL server table. This is a simple scenario where I need to move data from a csv file to a SQL server table.
I have 2 connection Managers - one for the location of CSV file and another for the SQL database connection string. I chose to Enable Package configuration. Two configurations -
a. Environment variable to store the database connection string.
b. SQL server table configuration for flat file connection manager.
The location of the CSV file is dynamic..so the idea is to change the path as and when needed in the SQL table.
No errors..everything works fine except that the properties of the flat file connection manager is not being pulled from the SQL server configuration table. It uses the location I had specified during design time.
How do I force the SSIS package to use the configuration specified in the SQL table always.
I've been spending a lot of time on this with no success. Please help..
Thanks
Karthik
Hi,
I have a SSIS package called "MyExport" stored on the SQL server 2005 standard SP1. I have created an SSIS package configuration stored in a SQL table msdb.dbo.SSIS Configurations, with the configurationFilter = "Export2" with some configuration values which I can change programmatically as needed.
How do I execute this package with the configuration in the SQL table? I don't seem to have any problem when the package configuration is stored in an .xml file. The documentation is very poor or non-existant on trying to do execute the package with configurations stored in SQL server. I just can't seem to get the proper syntax.
Can someone give me an example of a dtexec command for the above or maybe some c# code?
In Execute Package Utility, when you select the Configurations option, it pops up a dialog box for a FILE based configuration file (*.dtsconfig). There is NO WAY to access a configuration stored in the [dbo].[SSIS
Configurations] table - which is where the SQL based configurations are stored. If you could pick a SQL based configuration, you would then need to pick the ConfigurationFilter for the configuration you want to use.
It's the same when you try to create a new job in SQL Server Agent - you can't select the package configuration stored in SQL server. When you get to the configurations tab, you can only add a file based configuration.
I've checked BOL as well, and there are no examples or discussion of this that I can find.
If anyone can point me in the proper direction, I'd appreciate it.
Thanks.
I am new to SQL server 2005 and have a config question:
I am controlling database connection info using XML indirect config - no problems there.
Essentially I am going to have a number of packages that need to use a common file path, that might change from one server to the next, e.g. Server 1: C:sourceFiles versus Server 2: D:sourceFiles. Within this directory the filenames will remain static. So in the flat file connection manager I'd like to use a variable to reflect the folder - but I don't want to have to create this for each package.
So, I thought I would create a system environment variable and create expressions for the connection managers - something like %SOURCE_DIR% + "file.csv" - but this does not evaluate correctly.
So then i though I could use the SQL server configurations table with a configurationFilter SOURCE_DIR and appropriate configuration value - but then how do I access this in the flat file connection manager to create a dynamic file name?
So essentially I want a variable/property available globally to all my packages and potential flat file connection managers that help me to centrally control file path locations.
Any help would be most appreciated.
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.
Hi 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
I am getting following error when trying to install SQL express 2005 on XPSP2.
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine SIGMA-805539A79 Error:2147944122 (0x800706ba).
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=70342
I tied re-installing WMI using http://blogs.msdn.com/jpapiez/archive/2004/12/09/279041.aspx link but could not get it working.
Do i need IIS installed? Its not installed on this box...
please suggest something... i am stuck...
Thanks,
I have created a package configuration to read the connectionstring for the oledb connection manager.
Saved the file as Environment.dtsConfig in the same directory as the other packages.
Do the packages refer to the configuration file automatically?
Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?
Thanks
I would like to set package configuration.But dont know how to.
What i want to achieve is that i want to change connections based on test ,development and production environment of the same package...
How can i do that without me changing the connections manually to each task based on environment..
using package configuration i dont know how to...Please let me know.
Hello,
I'm developing some packages in SSIS and I need to implement a configuration file to load the server's connections and other parameters, so I can use this same connection for all packages.
Have anyone a step by step to perform this configuration ?
I exported a Connection Manager to a .dtsConfig file, but I don't know how to use this config file.
Any help is welcome !
Thanks,
Guber
Here is the scenario we are
trying to use; (it a bit long winded please bare with me)
There are 2 Packages Parent
and Child. Both have 2 Connection Managers (€śConfiguration€? & €śData Connection€?).
The €śData Connection€? has an invalid connection set to start with this is so
that we can deploy this to multiple places and use a SQL table to hold the
configuration.
At runtime €śConfiguration€? is
set to a valid SQL2005 database connection via the command line or in BI but €śData
Connection€? is left invalid.
The Package Configuration point
to an SQL server using €śConfiguration€? this has the connection string for the €śData
Connection€? Connection Manager (a valid connection string). The package errors
with can not acquire connection.
The first task is to put the
Connection string of the Connection manager €śConfiguration€? in to a variable.
Then the child package is called.
The Child package has the same
2 connections €śConfiguration€? & €śData Connection€? both are Invalid at this
time.
1) We use the Package
Configuration to populate a variable from the parent package which holds the €śConfiguration€?
connection string.
2) We use a Package
Configuration to populate then Configuration€? connection from the parent
package which holds the €śConfiguration€? connection.
3) We use a Package
Configuration to populate the €śData Connection€? Connection Manager using the €śConfiguration€?
connection. Which should be Set via step 2 here.
The First task on the child
package has a bit of script to check all the connection manager return a valid
connection to make sure database are up and running before connection. This
fails at the moment return in an error.
We want to use the same code
in multiple environments and as such don€™t want to make code changes that are really
only environment changes.
IE connection string to
servers. I can€™t seem to see anything saying we are doping anything wrong but if
the connections are all set to a valid connection before running this seems to
work. When invalid connection are used this seems to fall over.
I have created a package configuration to read the connectionstring for the oledb connection manager.
Saved the file as Environment.dtsConfig in the same directory as the other packages.
Do the packages refer to the configuration file automatically?
Does it make a difference if I use the dtexec to start the ssis packages since there is now a configuration file?
Thanks
Does it matter if the configuration is by xml,sql server,parent package variable,environment variable etc.
I use xml configuration which i kind of feel easy as i can change the connection directly by typing.Not used with others much..
Is there any security issue here .
Please let me know
Hi,
i am haveing package which is devloped in BIDS and i am executing the package from DotNet (Windows Application C#).during development my package is pointed to Server1,Database1.when we move the package to another test environment for a different server and database (Server2,Database2). we are giving the connection string details in dtsConfig file .but Package is not takeing the new connection string instead its takeing the old connection string (design time connection) and throwing error.what could be the problem
Invoking Code (C#)
--------------------
btn_SaExecute.Enabled = false;
string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation = "D:JeganSSIS PrjsInvokeTestInvokeTestData1.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
pkg.EnableConfigurations = true;
pkgResults = pkg.Execute();
Data.dtsConfig
----------------------
<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="PSLSDCJegan.thangadurai" GeneratedFromPackageName="Data1" GeneratedFromPackageID="{30ACE86F-D0D3-4575-AD46-2E9C2F34D46B}" GeneratedDate="9/14/2006 12:54:01 PM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[XXXX.YYYY].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=XXX;User ID=XXX;Password=XXX;Provider=MSDAORA.1;Persist Security Info=True;
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Thanks
Jegan
Hi all,
Hopefully a simple question. In my c# application i can call ImportConfigurationFile(<str>) to import an xml configuration file. How do i go about importing configuration settings from a SQL configuration package? I have created this but see no way to load it. I'd prefer to use the config file created in the SQL database.
Any help would be most appreciated.
Many thanks,
Grant
Hi all of us,
I'm looking any .dtsconfig file as sample for my first package configuration. I've got ssis packages which might be updated on development and then moved to production. First of all, I'd like dynamically change the connections but I don't know exactly how to do that. I'm taking a look on the wizard but when I choose a Sql Server request me a table.
Any sample or link for that would be welcomed!
TIA
Type:
Configuration File
New configuration file will be created.
File name:
C:Test.dtsconfig
Properties:
Package.Connections[Flat File Connection Manager].Properties[ConnectionString]
------------------------------------
So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?
Hi All,
I have couple of properties in my package Configuration
(1) Lets me know the Folder path - where I have file processing
(2) Connection String
When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration
But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment
I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.
Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.
Any help will be highly appreciated...thanks
hi everyone
I am putting my connection string in package configuration file, which i create through package configuration option.
But connection is not picking up value where as other varibles are picking up values from same file.
Is there is any other setting that we have to set for connection string.
Thanks and regards
Rahul Kumar
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
DEV
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Reporting Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
QA
Analysis Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Database Engine
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Common components
Integration Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Notification Services
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
Workstation Components
[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]
===================================
Hi, I'm facing a problem that I'm trying to solve. I report it in the following hoping somone can help me!
the context:
- I have a solution containing almost 20 packages
- I have a main package containing the sequences (Sequence Container) calling all others packages (Execute Package Task)
- every package has the connections to its own DBsource ; so the package X has the its own connections to DBsourceA and DBsourceB, the package Y has the its own connections to DBsourceB, DBsourceC, DBsourceD and the destination DB is unique for all the packages.
My problem is:
I have the necessity to define in the best way a config (one for all the dtsx, possibly) that allow me to manage easily the switch from the developing env to the deployment env, basically for the DB connections.
Which is the best way to do this and can anyone tell me the steps to follow?
I've been searching for an answer to my question quite some time now and I've not been able to figure it out yet.
Situation:
- I've created a SSIS package containing a bulk insert task.
- I've added a package configuration containing the appropriate connection manager (i.e. dev, beta or live)
- CreateDeploymentUtility = true
- I've copied the deployment folder to our beta server and I started the manifest file to install the package to the sql 2005 server, after that I specified the config file location and changed the value so the approriate connection manager is used.
- When I execute the package from the sql server the package doesn't read the value from the xml config file, it uses the connection which was originally specified in the package, whereas when I run the package from my BIDS it is reading the value from the xml config file?
I can't seem to figure out why this is happening? am I missing something here?
Thnx.
I'm facing a little problem concerning package configuration that is using a SQL table.
I have 4 exactly the same Db's on the same SQL server, each with another name of course (Dbipa, DbIpB, DbIpC & DbIpD).
My developers created 4 exactly the same SSIS packages with 4 exactly the same jobs, each for his own DB only the connection settings are different.
Now I was trying to manage this with only one SSIS package by using an SSIS Package configuration table in each DB.
I thought it would be possible to start the package with the following parameter
/SET Package.Connections[SQL].InitialCatalog;DBIPA OR
/SET Package.Connections[SQL].InitialCatalog;DBIPB OR
/SET Package.Connections[SQL].InitialCatalog;DBIPC OR
/SET Package.Connections[SQL].InitialCatalog;DBIPD
But this doesn't work because before he changes the initial catalog in the connection named SQL, he already did the loading of the SSIS_Configuration table. So he point always to the same config table.
Is there a way to change the connection before the package configuration is being executed.
Ludo Bernaerts
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection strings for all packages.
5) In master execute package, we generate an XML configuration file using SSIS -> Package Configurations... using Connection Managers -> Particular connection
6) Now, we change connection string to point to another database after adding this new OLE DB connection in Connection Manager of each and every package.
7) When we save, build and execute master package it is still executing migration packages against the old database. Please let me know what I am missing.
Hi,
Programmatically, how will i be able to know if a package is using package configurations?
Thanks.
I have a package that executes succesfully before package is configured.
but when package is configured to use configuration details either from SQL Server configuration table or XML configuration file, gives an error :
[Connection manager "XYZ"] Error: 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.".
Can anyone help me out with this ?
Experts !!! this is my second post, my previous post is still unanswered.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1793362&SiteID=1
Hi,
I am new to SSIS. I have an SSIS package which we are storing it in the application server ( not in SQL Server ). We are storing the database name etc.. in package configuration. Where should I store the configuration file? In app server or in the database server. Also please tell how to execute the package stored as file system.
Thanks
ramesh