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 showingis 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]
I have just tried this scenario and cannot reproduce it. I added several SQL configurations and all worked fine.
I wonder if you could try to repro, while running SQL Profiler - that way you may discover if there are any issues in updating your config table.
Donald
|||Thanks Donald --first off I have discovered a workaround. I think its way too risky and error prone to let the wizard write the actual values into the configuration table --you can imagine the chaos in large datawarehouse that has lots of new of records to enter into in the table!!!! Lets think it through--it I have say 5 new staging tables to add to the warehouse thats 65 manual entrys I would have to make with the wizard--no way is this workable
instead I prefer to sql script my new entrys and load in on hit
The workaround is to run the SQL insert script --then go to the wizard and change the configuration filter and go through to the endat this stage if you look at the variables they wont have changed since your last entry to the configuration table
Step1: Save the package
Step2: Exit out of visual studio completely and reopen again and go back into package
Hey presto the new values in the variables have changed
Can confirm this workaround only for SQL Server 2005
I have not tested for SQL Sever 2005 SP1
cheers Dave
No comments:
Post a Comment