PackageID and GUIDs in ExecuteSQL task

Am I looking at a potential bug here or do I not understand the feature properly? I have an ExecuteSQL task that inserts into a table for logging and includes the System::PackageID as one of the values. It's stored in my table as a uniqueidentifier. When I set the output variable in Parameter Mappings tab of the Execute SQL task to VarChar, all works great. WHen I set it to GUID as the data type in that tab, it outputs a different GUID than the actual System::PackageID variable.

-- Brian

This is not a bug, although several have thought it was. If you look at the system variable you will notice that even though it looks like a GUID the type of the variable is a string. This is why when you set the SQL task to use a string type it shows the correct value. However, when you set to GUID you don't get the value you expect because the SQL Task doesn't perform conversion for you so the value being placed in your uid field is actually a guid representation of the pointer not that GUID that is pointed to. Currently, if you want to set the GUID into a uid field in SQL I believe you need to use a script task because then you can get the conversion you expect.

HTH,

Matt

Package Variable Names are Case Sensitive

Hi I just discoved that Package Variable Names are case sensitive. Is it a feature or a bug. Microsoft ...Case Sensitive!!!!It's by design. Case insensitivity is OK for VB, but they also have a lot more control over the key words and the environment WRT locale. We avoided those issues by being case sensitive.
K

Package Configuration wizard:-SQL Configurations Configuration filter not working

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