SSIS Package Not Using Configuration From SQL Server Table
Apr 19, 2007
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..
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.
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.
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, 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'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.
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.
We have more than 100 packages which referenced many of the same data sources. In ideal situation, we would like to create three master filters based on the dev, test, and prod servers and all packages can just reference the same filters. However since one package normally only use a subset of master connections defined in the configuration files, if only those subset of connections are set up in the package, when the package is first open in BIDS, lots of errors will pop up saying that it could not find the connections defined in the configuration files. Even though this will not cost runtime error, it is still very annoying. Does anyone have better ideas on how to handle this?
I've been working with SSIS (SQL 2005 SP1) and I have a problem adding a package configuration of the SQL Server type. Upon completion of all steps in the wizard, the "Target Object" and "Target Property" attributes of this configuration remain empty. Hence, the package cannot set this configuration when being executed.
Does anyone know if this is a bug or have I missed something?
It 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?
Started: 10:48:56 AM Info: 2008-01-24 10:49:02.92 Code: 0x40016041 Source: SsisEndBatch Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc ENDBATCH TEST ENV.dtsConfig". End Info Warning: 2008-01-24 10:49:03.15 Code: 0x80012011 Source: SsisEndBatch Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid. End Warning
I have been asked by the powers that be to make sure that my configuration database gathers some auditing information. I have looked over these and have no idea where to find the information in SQL Server. If you could tell me the table and database that the informations are located in, I could write the T-SQL to find them. IMPORTANT you do not have to answer all of them, I am grateful to those that pick and choose even one to help me with.
SQL Server Auditing
General/Access Auditing Items
Software Install (DB): Verify that the sample and demonstration databases are not installed and remove the temporary setup files created by SQL Server setup process
Authentication and Access Control
Default user accounts & passwords (DB): Default user account passwords will be changed and will follow the corporate password standard for frequency of change, length and complexity User authentication (DB): Windows authentication is preferred. Mixed mode authentication can be used. User passwords (DB): All user passwords (mixed mode, SQL authentication, Windows authentication) will follow the corporate password standard for frequency of change, length and complexity. Sharing database user accounts (DB): Sharing of database user accounts is not permitted without an explicit exception documented and granted by IS Information Security. Normal users submitting jobs (DB): Normal user jobs should not be run as SA. Create a role-based policy for normal users that must be allowed to run jobs. They should be included in the role created for job submission.
Monitoring and Reporting
Auditing (DB): For all database installs after XX/XX/XXXX, Auditing should be turned on and at minimum set to log failed connections. Logs should be saved to a different hard drive than the one which data files are stored. SQL error log (DB): Processes should be in place to protect error log data so it can be reviewed for seven days prior to being overwritten or deleted from the system.
Networking
Cross-database ownership chaining (DB): Cross-database ownership chaining is disabled. Code of stored procedures, triggers and views (DB): Who has access Public access to SYSXLOGINS and SYSDATABASES tables: The public role will not have access to the SYSXLOGINS or the SYSDATABASES tables. Public access to SYSOBJECTS and SYSCOLUMNS tables: The public role will not have access to the SYSOBJECTS and SYSCOLUMNS tables. Public access to stored and extended stored procedures: The public role will not have access to stored or extended stored procedures. Public access to xp_regread and xp_instance_regread: Restrict public role access to xp_regread and xp_instance_regread. Public group access to mswebtasks table: The public group will not have insert, update, delete or select permissions to the msdb.dbo.mswebtasks table. Temporary directories: For all database installs after xx/xx/xxxx, all temporary directories will be cleaned on a periodic basis. Auditing II: Auditing should be turned on and at minimum set to log failed connections. Logs should be saved to a different hard drive than the one which data files are stored. SQL Server instances visibility: All SQL Server instances should not be visible across the network. They should be set up with the hidden option activated. Default ports: SQL Server should be configured to not use the default ports.
How do I create, say an "update statistic" maintenace package that works on multiple servers and databases?
For example, how do I get the package to operate on the following servers and tables: ServerA, tableX, tableY, tableZ ServerB, tableM, tableN
Also, I would like to save the package configuration and store it on SQL Server. So if I want to add more servers or tables to the list, the SSIS package will pick it up at run time.
We have 6 Â SSIS packages which populates different sets of table by reading different set of excel file.We need to have a master SSIS package which will have the configuration (say xml) which consists of database connection details and file path details of child packages.what will the best way to achieve the desire results.
Package 1 use File x package 2 Â use File y package 3 use File z .... package 6 use File a
The parent ssis package will have xml file as configuration which will store the all the six different file details for child packages along with database connection string.Is the above option feasible . or what approach will be the best possible way to achieve the results.Since the Triggering of SSIS package (Master SSIS package) will be from SQL Job
I have been trying to get a variable in my package to retrieve its data from the SQL configuration table.. so that in future i am able to alter them all easily.
On the SSIS > Package Configurations menu, there seems to only be the option to pass a variable FROM the package TO SQL
whereas what i want to do is infact the opposite...
I have two SSIS projects each with different packages. I have setup the packages with configurations stored in a SQL Server table in MSDB. When I create the configuration on the second project it overwrites all of the first projects configurations. Is there a way to to get two different project configurations stored in the same SQL Server table? Any help would be greatly appreciated. Thanks!
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
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]
I have 4 packages in one solution. I have created one configuration table in SQL server for all the packages. For each package, I have a different configuration filter in the same table. Everything works fine in the development environment.But when I create a deployment utility and deploy the packages, the values are not picked from the configuration table although I have re-created the configuration table in the new database.It tries to pick up the values from the database used in the development environment and thus it fails. I have changed all the connection strings in the package after deployment but still of no use.
I am not getting where I am going wrong. Any help would be highly appreciated.
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.
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)?
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?
dear list can anyone figure out a workaround as to why OLEDB Provider MSDORA cannot store passwords I have all the info stored in a table (Project Real Best Practise) The user id I have stored in string ConfiguredValue gets transfered to OLEDB Provider MSDORA named (SQL_REAL_Source_myoradb) but not the password To workaround this bug only with MSDORA can aynonne sugest a setting I should use in package security ie the default is EncryptSensitiveWithUserKey?
thanks Dave
CREATE TABLE [admin].[Configuration]( [ConfigurationFilter] [nvarchar](255) NOT NULL, [ConfiguredValue] [nvarchar](255) NULL, [PackagePath] [nvarchar](255) NOT NULL, [ConfiguredValueType] [nvarchar](20) NOT NULL ) ON [PRIMARY]
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.
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?
The master package has a configuration file, specifying the connect strings The master package passes these connect-strings to the child packages in a variable Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s. We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1: The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2: When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3: When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4: When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
