SQL Server 2005 - SSIS: Global Package Configuration
Jan 7, 2008
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.
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.
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
We are migrating our production environment from 32-bit SQL Server 2005, Windows 2003 server to 64-bit SQL Server 2005, Windows 2003 server environment with 4GB of RAM. We have recompiled the SSIS packages to run in 64-bit mode and stored them in database. But one of the package fails with initial information as
Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 34720 buffers were considered and 34720 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
And the subsequent error messages received as
Error Message 1
A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota.
Error Message 2
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I want to run ssis package in sql server 2005 agent. i've made a new JOB with 3 steps ( 3 ssis package)
But when i run the job, i see only 2 "actions step" in the windows :
Start job ..... Execute job ....
I would like to see at least the execution status of every ssis package and if it's possible, the execution of every task ( to see quickly if there's some error and what's wrong)
We have an existing OLD System in SQL Server 2000 DTS Packages. The Whole application runs on DTS. There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files. Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves. To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System. But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data. 1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application. 2. Some wants to store it in a Table in the Same Database of this Application. 3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Hi, My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1 GO reconfigure GO
------------------------------------------------------------- -- Database Mail Simple Configuration Template. -- -- This template creates a Database Mail profile, an SMTP account and -- associates the account to the profile. -- The template does not grant access to the new profile for -- any database principals. Use msdb.dbo.sysmail_add_principalprofile -- to grant access to the new profile for users who are not -- members of sysadmin. -------------------------------------------------------------
-- Profile name. Replace with the name for your profile SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang'; SET @SMTP_servername = 'smtp.cgdnow.com'; SET @email_address = 'vdang@cdgnow.com'; SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist. IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name) BEGIN RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1); GOTO done; END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name ) BEGIN RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ; GOTO done; END;
-- Start a transaction before adding the account and the profile BEGIN TRANSACTION ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ; GOTO done; END
-- Add the profile EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp @profile_name = @profile_name ;
IF @rv<>0 BEGIN RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1); ROLLBACK TRANSACTION; GOTO done; END;
-- Associate the account with the profile. EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @profile_name, @account_name = @account_name, @sequence_number = 1 ;
IF @rv<>0 BEGIN RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ; ROLLBACK TRANSACTION; GOTO done; END;
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'm busy rewriting DTS packages as SSIS packages. As and when I finish a package I run it in debug mode via Microsoft Visual Studio and then examine the Exection Results to see the messages generated.
Now it may or may not matter how I run the package but the following warning has been generated :-
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
I am exporting 350 tables data from SQL Server 2005 to Access 2003.and getting the below error.
SSIS package "Package2.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Error: 0xC0202009 at Package2, Connection manager "DestinationConnectionOLEDB": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
Error: 0xC020801C at Data Flow Task, Destination 64 - CLIMBINGEXP [8065]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Destination 64 - CLIMBINGEXP" (8065) failed the pre-execute phase and returned error code 0xC020801C.
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.
