BUG Multi-value field in SP2

Hi,
There seems to be a bug with the multi-valed fields when only one value is
selected and that value contains only spaces. I get the message 'Please
select a value for the parameter...'. It works when I test it in Visual
Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
Any ideas ? Suggestions ?
Regards
AlainOn Apr 6, 12:05 pm, "Alain Magnan" <AMag...@.Hatch.caGARBAGE> wrote:
> Hi,
> There seems to be a bug with the multi-valed fields when only one value is
> selected and that value contains only spaces. I get the message 'Please
> select a value for the parameter...'. It works when I test it in Visual
> Studio but it doesn't work on the Web. I'm using SQL Server 2005 with SP2.
> Any ideas ? Suggestions ?
> Regards
> Alain
Are you using 2 columns in the dataset that sources the multi-select
parameter? If the value field is something like 0, it might not error
out. Something like this might work (in the dataset; where Name is the
displayed item and NameID is the parameter value):
Name NameID
" " 0
"Bob" 1
"John" 2
"Bill" 3
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant

BUG in Integration Services

Problem

When you have a SSIS package that contains a connection from a data source, this connection is not updated when the data source changes based on a configuration change.

Situation :

A SSIS solution contains 3 configurations : Development, Test, Production. You can create those configurations in configuration manager of the solution.

The SSIS project contains one Data source. It doesn't really matter what type but I take SQL Server. The database server in development is SQL_DEV, in test is SQL_TEST and in production is SQL_PROD. Initially they are for all configurations the same. You can specify those values by changing the active configuration and then editing the Data source.

In the SSIS package (DTSX), you can create a connection manager based on a Data source.

If you change the Data source, the connection manager is also changed. If you change the Data source by changing the active configuration, the connection manager is not being updated.

If you think this isn't a big issue think big. We have 4 configuration, 10 shared Data sources and 25 DTSX packages. That would give a maximum of 1000 settings (4 x 10 x 25). Using this method it can be reduced to 40 (4 x 10). Of course this is a theoretical but it is very common to have the destination data source re-used on all packages, which still would be 100 settings (4 x 25)

Steps to reproduce

- create a new SSIS project

- In the solution explorer, create a new Data source named TestSource.

- In the connection managers window of Package.dtsx, create a new connection from a Data source.

- Make some changes in to TestSource.ds under the Data Sources. For example change the server or the database.

- Verify that those changes are also in the package.

- in the solution explorer, right click the solution and select configuration manager

- under active solution configuration, create a new configuration named test.

- Set the copy settings from : development

- Verify that Create new project configuration is checked.

- click OK and close.

- Notice that the active configuration is now Test

- Make some changes the Testsource.ds like a different server.

- Verify that those changes are also in the package.

- Make the development configuration as active.

- Notice that the Testsource.ds contains now the original settings.

- You will notice that the connection manager still contains the "test" settings and not the development settings.

- If you create a deployment utility it will still contains the wrong values.

with regards,

Constantijn Enders

Don't use Data Sources. Just use the connection managers and use package configurations to configure them.

Breaking apart Column into rows

I have a column that has text delimited by a percent sign that I wish
to turn into rows.
Example:
A column contains ROBERT%CAMARDA, I want to turn that into two rows,
one row with ROBERT and antoher row with CAMARDA.
I will have source rows that have zero, one, or many percent sign
delimiters that will correspond to that many rows (One percent sign
will create 2 rows, 2 percent signs will create 3 rows and so forth).

Any thoughts?
TIA
Robrcamarda (rcamarda@.cablespeed.com) writes:
> I have a column that has text delimited by a percent sign that I wish
> to turn into rows.
> Example:
> A column contains ROBERT%CAMARDA, I want to turn that into two rows,
> one row with ROBERT and antoher row with CAMARDA.
> I will have source rows that have zero, one, or many percent sign
> delimiters that will correspond to that many rows (One percent sign
> will create 2 rows, 2 percent signs will create 3 rows and so forth).

Have a look at an article on my web site:
http://www.sommarskog.se/arrays-in-sql.html.

Specifically, look at "List-of-strings" and "Unpacking a table column".

You did not say which version of SQL Server you are using. The article
is written for SQL 2000. SQL 2005 provides a new operator CROSS APPLY
which is good for the column bit. In SQL 2000, you will have to run
it row by row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx