Report With SSIS Package Having Indirect Package Configuration Setup
Sep 10, 2007
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.
Is it possible to create an indirect Package Configuration by using the /CONF switch when running a package via dtexec. Here is what I would like to do: 1. Use an Indirect SQL Server configuration type 2. Instead of storing the connection string in an env. variable, I want to specify it at run time using the /conf switch
In other words, are there any alternatives to storing the configration connection string on the server?
I need to configure my package using SSIS Configurations. When I opened SSIS Configuration wizard, press ADD and selected Xml Configuration, I found two options.
One being : Specify configurations directly.
next : config location is stored in env variable.
How to use the second option?[ env variable]. Say I want to configure my connection string for my destination database [ OLEDB Destination Component]. How to do that using second option.
which follows the standard of : db connections, config table, filter
This works by the way on my client but on the dev server the error is:
Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.
I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.
I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)
Appreciate any help. I'm trying to deploy this on the Dev server for testing.
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?
I am trying to create indirect package configurations so that:
1) My configuration data is stored in a SQL Server table, and
2) The location of that table is stored in an environment variable.
Achieving #1 is easy, but when I hit the "Configuration location is stored in an environment variable", I strike problems. I can specify a environment variable no problems, but there doesn't seem to be anywhere in the documentation to describe the syntax of WHAT should go in that environment variable.
If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?
I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..
My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.
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?
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..
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.
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?
I keep getting a failure when trying to run my agent which is trunning an SSIS 2005 package. I have checked the logins and appear to have given the right logins owner to the msdb database and my other databases but still getting this error:
03/09/2006 10:13:31,Run EBN Process,Error,0,BG-22SQL,Run EBN Process,(Job outcome),,The job failed. The Job was invoked by User domainmyuserid. The last step to run was step 1 (Run EBN SSIS Package).,00:00:02,0,0,,,,0 03/09/2006 10:13:31,Run EBN Process,Error,1,BG-22SQL,Run EBN Process,Run EBN SSIS Package,,Executed as user: domainaccount_we_setup_to_run_all_sql_services_on_this_server. The package execution failed. The step failed.,00:00:02,0,0,,,,0
Is it the package that is failing or permission issue?
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.
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 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.
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;
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
The report having SSIS package as source works fine on my local 32 bit windows xp machine from Report Manager. But same doesn't work from 64bit windows 2003 machine... fails with below error... what could be an issue?
An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)
The same works fine when previewed from visual studio on 64 bit machine.
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 a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
