Parent Package Variable issue?

I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.

Is this a know issue? Is it by design?Yep, I see the same thing.|||Good to know I'm not crazy! Thanks.|||Look for a fix in SP3, perhaps.

There is already a bug filed for this issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:

>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is no
t
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:
>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Parse field into multiple rows

Hello,
I am loading data from our MS Active Directory into our data
warehouse. (check out Mircosofts's Logparser, it can pull data from
ADS, server event logs and more. It can also create text files or load
directly to SQL. Its free and a pretty useful tool)
There is a field that contains the direct reports of a manager. The
direct report users are delimited by a pipe symbol.
I want to breakup the field into multple rows. There can be none, one
or many direct report users in this field.
<disclaimer>
This is a snippet of an example. This is only an example. I know that
I have not defined PK nor indexes. My focus is how to solve a problem
of parsing a field that has multple values into multple rows.
</disclaimer>
Thanks for any help in advance.
Rob

CREATE TABLE "dbo"."F_ADS_MANAGERS"
(
"MANAGER_KEY" VARCHAR(255) NULL,
"DIRECT_REPORTS_CN" VARCHAR(255) NULL
);

INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)
VALUES ('CN=Marilette, 'CN=Robert
D,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Robert
Camarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelle
C,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia
B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')

I want to end up with 5 rows, 1 row for each user that is seprated by
the PIPE symbol.
CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Robert
Camarda,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu
CN=Marilette CN=Lee K,OU=TechnologyGroupHere is one method to split the list using a recursive CTE (this assumes SQL
Server 2005). It is not the fastest, but with a small size list to split it
should be OK.

WITH Managers
(manager_key, direct_reports_cn, start_pos, end_pos)
AS
(
SELECT manager_key,
direct_reports_cn + '|',
1,
CHARINDEX('|', direct_reports_cn + '|')
FROM F_ADS_MANAGERS
UNION ALL
SELECT manager_key,
direct_reports_cn,
end_pos + 1,
CHARINDEX('|', direct_reports_cn, end_pos + 1)
FROM Managers
WHERE CHARINDEX('|', direct_reports_cn, end_pos + 1) 0
)
SELECT manager_key,
SUBSTRING(direct_reports_cn,
start_pos,
end_pos - start_pos) AS direct_report
FROM Managers
WHERE end_pos 0;

Erland Sommarskog has a very good article for arrays and lists that covers
various techniques and analysis:
http://www.sommarskog.se/arrays-in-sql.html
HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Thanks for the link to Erland's article's, it is very handy.
I tried your suggestion but I am confused by the WITH.
I tried
SELECT * FROM F_ADS_MANAGERS
WITH Managers
<snip your code>
and I got an error. Haven't seen this before so I am not sure how to
trouble shoot.
Error I received:
Msg 336, Level 15, State 1, Line 2
Incorrect syntax near 'Managers'. If this is intended to be a common
table expression, you need to explicitly terminate the previous
statement with a semi-colon.

On Jun 27, 10:01 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Here is one method to split the list using a recursive CTE (this assumes SQL
Server 2005). It is not the fastest, but with a small size list to split it
should be OK.
>
WITH Managers
(manager_key, direct_reports_cn, start_pos, end_pos)
AS
(
SELECT manager_key,
direct_reports_cn + '|',
1,
CHARINDEX('|', direct_reports_cn + '|')
FROM F_ADS_MANAGERS
UNION ALL
SELECT manager_key,
direct_reports_cn,
end_pos + 1,
CHARINDEX('|', direct_reports_cn, end_pos + 1)
FROM Managers
WHERE CHARINDEX('|', direct_reports_cn, end_pos + 1) 0
)
SELECT manager_key,
SUBSTRING(direct_reports_cn,
start_pos,
end_pos - start_pos) AS direct_report
FROM Managers
WHERE end_pos 0;
>
Erland Sommarskog has a very good article for arrays and lists that covers
various techniques and analysis:http://www.sommarskog.se/arrays-in-sql.html
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||Here are a few notes that will help you understand the common table
expressions and why you get the error:

- It is required to terminate the statement before the WITH keyword defining
the common table expression with a semicolon. It is because the WITH keyword
has other uses. In your case if you end the select statement in the line
before WITH using ; it will work.
- I am not sure if you understand how CTEs work. CTEs are not materialized
and work pretty much like derived tables (with more functionality, like the
recursion used in this method). They only get defined before the statement
the uses them, so you can reference the CTE only in that statement. There is
a lot more to it, and if you decide to use this method I would suggest to
read more on CTEs in SQL Server Books OnLine.
- If you need to materialize the result of the CTE, you can use INSERT to
insert the data into a temporary table, or just define a view based on the
CTE (or you could also create a function based on the CTE).

Here is a complete example using the approach to define a view based on the
CTE:

CREATE TABLE F_ADS_MANAGERS
(
MANAGER_KEY VARCHAR(255) NULL,
DIRECT_REPORTS_CN VARCHAR(255) NULL
);

INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)
VALUES ('CN=Marilette',
'CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=edu|
CN=Robert Camarda,OU=TechnologyGroup,DC=strayer,DC=edu|
CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=edu|
CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|
CN=Lee K,OU=TechnologyGroup');

GO

CREATE VIEW ManagersWithReports
(manager_key, direct_report)
AS
WITH Managers
(manager_key, direct_reports_cn, start_pos, end_pos)
AS
(
SELECT manager_key,
direct_reports_cn + '|',
1,
CHARINDEX('|', direct_reports_cn + '|')
FROM F_ADS_MANAGERS
UNION ALL
SELECT manager_key,
direct_reports_cn,
end_pos + 1,
CHARINDEX('|', direct_reports_cn, end_pos + 1)
FROM Managers
WHERE CHARINDEX('|', direct_reports_cn, end_pos + 1) 0
)
SELECT manager_key,
SUBSTRING(direct_reports_cn,
start_pos,
end_pos - start_pos) AS direct_report
FROM Managers
WHERE end_pos 0;

GO

SELECT manager_key,
direct_report
FROM ManagersWithReports;

GO

DROP VIEW ManagersWithReports;
DROP TABLE F_ADS_MANAGERS;

GO

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.

But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.

Thanks

Set up four execute package tasks that point to each child package. Then use a variable precedence constraint to determine which execute package task to run. You could make the variable an integer and then use something like: @.ChildPackage == 1, @.ChildPackage == 2, etc...|||

One problem I see with this approach is that, If I develop another child package then I have to modify the master package to include this new child package and re-deploy. as the number of packages increases, it will be difficult to maintain. Basically I just want one execute package task which should be configurable ( say which package to execute or path of the package to execute)

Thanks

|||Could you use two Execute Package tasks, one configured for executing on the file system, the other for the database? Then use precedence constraints to control which one executes. So you'd pass in two variables - one being the connection to the package, the other setting a variable that controls which package executes.

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.

But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.

Thanks

Set up four execute package tasks that point to each child package. Then use a variable precedence constraint to determine which execute package task to run. You could make the variable an integer and then use something like: @.ChildPackage == 1, @.ChildPackage == 2, etc...|||

One problem I see with this approach is that, If I develop another child package then I have to modify the master package to include this new child package and re-deploy. as the number of packages increases, it will be difficult to maintain. Basically I just want one execute package task which should be configurable ( say which package to execute or path of the package to execute)

Thanks

|||Could you use two Execute Package tasks, one configured for executing on the file system, the other for the database? Then use precedence constraints to control which one executes. So you'd pass in two variables - one being the connection to the package, the other setting a variable that controls which package executes.

Paramters with Multiple Values

I know I must be missing something obvious but I cannot find it in the
documentation. How do I allow the user to input multiple values for a report
parameter. For example, the main query has this for test criteria:
where CustID in(100, 200, 300)
so I changed to:
IN(@.CustId) expecting to input 100,200,300
but instead I get an error "Application uses a value of the wrong type for
the current operation"
? ThanksTo do that you have to write dynamic sql and your parameter has to be a
varchar... the reason you are getting an error is you are comparing an
INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
"100,200,300" to integers.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> I know I must be missing something obvious but I cannot find it in the
> documentation. How do I allow the user to input multiple values for a
report
> parameter. For example, the main query has this for test criteria:
> where CustID in(100, 200, 300)
> so I changed to:
> IN(@.CustId) expecting to input 100,200,300
> but instead I get an error "Application uses a value of the wrong type for
> the current operation"
> ? Thanks
>|||So does that mean if it were a varchar column it would accept the commas and
treat them as delimiters between separate values ie 100 OR 200 OR 300?
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> To do that you have to write dynamic sql and your parameter has to be a
> varchar... the reason you are getting an error is you are comparing an
> INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> "100,200,300" to integers.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
>> I know I must be missing something obvious but I cannot find it in the
>> documentation. How do I allow the user to input multiple values for a
> report
>> parameter. For example, the main query has this for test criteria:
>> where CustID in(100, 200, 300)
>> so I changed to:
>> IN(@.CustId) expecting to input 100,200,300
>> but instead I get an error "Application uses a value of the wrong type
>> for
>> the current operation"
>> ? Thanks
>>
>|||No, it would treat it as one string... the easiest way to do a query with a
parameter that contains multiple integer values is dynamic sql like...
DECLARE @.CustID VARCHAR(100)
DECLARE @.sql VARCHAR(1000)
SET @.CustID = '100,200,300'
SELECT @.sql = 'SELECT * FROM Customer WHERE CustID IN (' + @.CustID + ')'
EXEC(@.SQL)
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O1XluJDyEHA.2212@.TK2MSFTNGP15.phx.gbl...
> So does that mean if it were a varchar column it would accept the commas
and
> treat them as delimiters between separate values ie 100 OR 200 OR 300?
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> > To do that you have to write dynamic sql and your parameter has to be a
> > varchar... the reason you are getting an error is you are comparing an
> > INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> > "100,200,300" to integers.
> >
> >
> > "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> > news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> >> I know I must be missing something obvious but I cannot find it in the
> >> documentation. How do I allow the user to input multiple values for a
> > report
> >> parameter. For example, the main query has this for test criteria:
> >> where CustID in(100, 200, 300)
> >> so I changed to:
> >> IN(@.CustId) expecting to input 100,200,300
> >> but instead I get an error "Application uses a value of the wrong type
> >> for
> >> the current operation"
> >>
> >> ? Thanks
> >>
> >>
> >
> >
>

parameters with multi value and navigation

Hello.

I have a report with parameters include multiple value.

I configure "Jamp To Report" under navigation and I would like to forward to a new reports the same parameters user select in the source report undel multiple value parameters.

in the text box I use =join(Parameters!Ds.Label ,", ") but how can I forward the selected parameters to the new report

Thanks

Idan

Please read this related posting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

-- Robert

parameters with multi value and navigation

Hello.
I have a report with parameters include multiple value.
I configure "Jamp To Report" under navigation and I would like to
forward to a new reports the same parameters user select in the source
report undel multiple value parameters.
in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
forward the selected parameters to the new report
Thanks
IdanHi,
The parameter used in both the query should be same.
Amarnath
"Idan" wrote:
> Hello.
> I have a report with parameters include multiple value.
> I configure "Jamp To Report" under navigation and I would like to
> forward to a new reports the same parameters user select in the source
> report undel multiple value parameters.
>
> in the text box I use =join(Parameters!Ds.Label ,", ") but how can I
> forward the selected parameters to the new report
> Thanks
> Idan
>

Parameters using like

Hi,
I have a parameter named County. County, can have multiple values in it -
ex: 01, 02, 03.
I need to somehow use a like statement with this parameter so that it will
find all the counties in this field. I tried like%@.County% but this doesn't
work.
Anybody have any suggestions'
ThanksJill,
I suspect that what you'd like to do is something like this:-
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (1,2,3,4,5)
and then your thinking probably goes that you'd want to replace (1,2,3,4,5)
with a parameter such as:
SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (@.CountyParameter)
You could make this work like I'm about to show you. BUT DONT!
EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
WHERE CountyID IN (' + @.CountyParameter+ ')')
The reason not to do this is that this is very insecure from attacks from
SQL INJECTION. Just consider what would happen if someone passed
4);INSERT INTO CountiesTable(CountyName,
CountyID)VALUES('DisneyFantasyCounty',667)--
as the value for @.CountyParameter (or something far worse).
So having told you what not to do - the correct thing to do is create a
function on the Server such as this one taken directly from the
'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages 534 -
537)
CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
RETURNS @.paramtable
TABLE ( Id int )
AS BEGIN
DECLARE @.len int,
@.index int,
@.nextindex int
SET @.len = DATALENGTH(@.delimString)
SET @.index = 0
SET @.nextindex = 0
WHILE (@.len > @.index )
BEGIN
SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
INSERT @.paramtable
SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
SET @.index = @.nextindex + 1
END
RETURN
END
What this function does is that you pass it a parameter of a delimited
string such as your counties '1;2;6;' and returns a table. This table can
then be joined into your query. This approach is safer from SQL Injection
attacks. And so your Query for the DataSet becomes:
SELECT CountyID, CountyName FROM CountiesTable
INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
ON CountiesTable.CountyID= ParamParserFn.Id
I hope this is able to help you. More details as I mentioned are available
in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
Services. (see http://www.sqlreportingservices.net ). I would heartily
recommend our book to you as a valuable resource that comes with 2.5 GB of
DVD content and video demonstrations, and we especially concentrate on
security matters throughout.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
> Hi,
> I have a parameter named County. County, can have multiple values in it -
> ex: 01, 02, 03.
> I need to somehow use a like statement with this parameter so that it will
> find all the counties in this field. I tried like%@.County% but this
> doesn't
> work.
> Anybody have any suggestions'
> Thanks
>|||We will also natively support multiple values in queries in the next release
of Reporting Services.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Blackburn (www.sqlreportingservices.net)"
<http://www.sqlreportingservices.net> wrote in message
news:u6Py%239gxEHA.2040@.tk2msftngp13.phx.gbl...
> Jill,
> I suspect that what you'd like to do is something like this:-
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (1,2,3,4,5)
> and then your thinking probably goes that you'd want to replace
> (1,2,3,4,5) with a parameter such as:
> SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (@.CountyParameter)
> You could make this work like I'm about to show you. BUT DONT!
>
> EXECUTE ('SELECT CountyID, CountyName FROM CountiesTable
> WHERE CountyID IN (' + @.CountyParameter+ ')')
> The reason not to do this is that this is very insecure from attacks from
> SQL INJECTION. Just consider what would happen if someone passed
> 4);INSERT INTO CountiesTable(CountyName,
> CountyID)VALUES('DisneyFantasyCounty',667)--
> as the value for @.CountyParameter (or something far worse).
>
>
> So having told you what not to do - the correct thing to do is create a
> function on the Server such as this one taken directly from the
> 'Hitchhiker's Guide to SQL Server 2000 Reporting Services' (see pages
> 534 - 537)
>
> CREATE FUNCTION ParamParserFn( @.delimString varchar(255) )
> RETURNS @.paramtable
> TABLE ( Id int )
> AS BEGIN
> DECLARE @.len int,
> @.index int,
> @.nextindex int
> SET @.len = DATALENGTH(@.delimString)
> SET @.index = 0
> SET @.nextindex = 0
> WHILE (@.len > @.index )
> BEGIN
> SET @.nextindex = CHARINDEX(';', @.delimString, @.index)
> if (@.nextindex = 0 ) SET @.nextindex = @.len + 2
> INSERT @.paramtable
> SELECT SUBSTRING( @.delimString, @.index, @.nextindex - @.index )
> SET @.index = @.nextindex + 1
> END
> RETURN
> END
> What this function does is that you pass it a parameter of a delimited
> string such as your counties '1;2;6;' and returns a table. This table can
> then be joined into your query. This approach is safer from SQL Injection
> attacks. And so your Query for the DataSet becomes:
> SELECT CountyID, CountyName FROM CountiesTable
> INNER JOIN ParamParserFn(@.@.CountyParameter) ParamParserFn
> ON CountiesTable.CountyID= ParamParserFn.Id
>
> I hope this is able to help you. More details as I mentioned are available
> in Chapter 11 of the Hitchhiker's Guide to SQL Server 2000 Reporting
> Services. (see http://www.sqlreportingservices.net ). I would heartily
> recommend our book to you as a valuable resource that comes with 2.5 GB of
> DVD content and video demonstrations, and we especially concentrate on
> security matters throughout.
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
>
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:302F82DB-80A3-411E-8A45-34B6770290DC@.microsoft.com...
>> Hi,
>> I have a parameter named County. County, can have multiple values in
>> it -
>> ex: 01, 02, 03.
>> I need to somehow use a like statement with this parameter so that it
>> will
>> find all the counties in this field. I tried like%@.County% but this
>> doesn't
>> work.
>> Anybody have any suggestions'
>> Thanks
>

Parameters passed to Stored Procedure from ASP

I'm relatively new to stored procedure writing. My situation is an ASP page allowing multiple selections from a <select> option passing 1 to x number of options to a stored proc. for the WHERE clause.
For instance, a user selects 1,3,5 and 6. These would need to be passed to the sp and then:

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

This sp ties into a Crystal Report and in the above scenario, should return data for colums equal to 1 3 5 and 6. If only 1 and 6 had been selected on the ASP page, then only those two would be assigned a value in the sp.

Any suggestions?
Thanks in advance...

IIS 5.0, Win 2k, MSSQL 7.0Just create an ado connection/command objects and execute the stored procedure. What are the ranges for the parameters ?|||Looks like you want to say something like

WHERE col1 IN (@.param1, @.param2, @.param3, @.param4)

Or

WHERE col1 IN (@.param1, @.param2)

whatever the case may be...is that right?

Or are they diferent columns|||Well, if the ASP was passing three different parameters (Start Date, End Date, Details) they would be passed to the sp like(whereas the strStartDate, strEndDate and strDetails were assigned the Request.Form values):

Set ThisParam = StoredProcParamCollection.item(1)
ThisParam.SetCurrentValue cstr(strStartDate), 12

Set ThisParam = StoredProcParamCollection.item(2)
ThisParam.SetCurrentValue cstr(strEndDate), 12

Set ThisParam = StoredProcParamCollection.item(3)
ThisParam.SetCurrentValue cstr(strDetails), 12

However, in my scenario, I need to allow for multiple selections in one Request.Form("select") collection so to speak passed to the sp.

Does that make sense??
:-\|||What is the maximum number of selections in the select box - and will this keep growing ?|||The select box as 12 selections. The user can pick as few as one or as many as all. Basically, any combination. Say they pick 1 & 2. I need to pass those selections to the sp and use those parameters in the where

WHERE [columnname] = @.parameter1 or [columnname] = @.parameter2 or [columnname] = @.parameter3(parameter 3 remains default value since only 1 and two were passed in)

(The parameters are assign the passed values or remain default if no value passed)|||How come you don't ask for a variable result set from the SELECT..can't they pick their own fields too?

Don't want to use the D word....

Have you run a sql statement with all 13 parameters?

Can we see the sproc...

Is it like CREATE PROC mySproc @.Param1 = null, @.Param2 = null

Maybe you can pass all of them

and do WHERE Col1 = ISNULL(@.Param1,Col1) AND...|||You could set up one parameter and pass a delimited string containing all your selections.

You then split the string up and use the in statement to do your selection.

It's not pretty but it will work.

Let me know if you want details of how to do this.|||Brett & rokslide, thanks for the help. Basically, the options aren't added from a db connection into the asp page. So, hard coding the 12 values (which never change as they are campus locations) isn't a problem. So, the intent was to pass any combination of selected campuses, pass them to a sp and generate a report for the campuses selected.

...FROM [tablename]
WHERE (Number = @.param1) OR (Number = @.param2) OR (Number = @.param3) OR (Number = @.param4) etc...

Is basically what I need to do, but I like the delimited idea and splitting the string and doing the select through iteration.

Parameters opening the same datasets over and over again..

Hello:
I have a report with multiple tables, one table per page. On the page header
I need to show some information that is coming from a dataset, several
records like first name, last name, file#, etc..
I've a single dataset in my report, which has the data for all the tables,
and it includes the information I need in the header.
My page header expression is the sum of the values of some parameters that
gets their values from the dataset.
The problem is that the stored procedure I'm using to get the data is
executed once for each parameter, instead of executing it only once and get
the values for all the parameters locally.
Is there any way to improve this behavior?
Thanks,
Daniel Bello Urizarri.On Mar 26, 4:09 pm, "Daniel Bello" <dburiza...@.yahoo.es> wrote:
> Hello:
> I have a report with multiple tables, one table per page. On the page header
> I need to show some information that is coming from a dataset, several
> records like first name, last name, file#, etc..
> I've a single dataset in my report, which has the data for all the tables,
> and it includes the information I need in the header.
> My page header expression is the sum of the values of some parameters that
> gets their values from the dataset.
> The problem is that the stored procedure I'm using to get the data is
> executed once for each parameter, instead of executing it only once and get
> the values for all the parameters locally.
> Is there any way to improve this behavior?
> Thanks,
> Daniel Bello Urizarri.
You might be able to avoid the multiple parameters, if I understand
you correctly, by using aggregates to reference values in the dataset.
Something like this might help.
=Max(Fields!FirstName.Value, "DataSetName")
=Max(Fields!LastName.Value, "DataSetName")
=Max(Fields!FileNum.Value, "DataSetName")
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1174959834.202639.33660@.p77g2000hsh.googlegroups.com...
> On Mar 26, 4:09 pm, "Daniel Bello" <dburiza...@.yahoo.es> wrote:
>> Hello:
>> I have a report with multiple tables, one table per page. On the page
>> header
>> I need to show some information that is coming from a dataset, several
>> records like first name, last name, file#, etc..
>> I've a single dataset in my report, which has the data for all the
>> tables,
>> and it includes the information I need in the header.
>> My page header expression is the sum of the values of some parameters
>> that
>> gets their values from the dataset.
>> The problem is that the stored procedure I'm using to get the data is
>> executed once for each parameter, instead of executing it only once and
>> get
>> the values for all the parameters locally.
>> Is there any way to improve this behavior?
>> Thanks,
>> Daniel Bello Urizarri.
>
> You might be able to avoid the multiple parameters, if I understand
> you correctly, by using aggregates to reference values in the dataset.
> Something like this might help.
> =Max(Fields!FirstName.Value, "DataSetName")
> =Max(Fields!LastName.Value, "DataSetName")
> =Max(Fields!FileNum.Value, "DataSetName")
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Enrique:
You can not use fields on the headers, that is why I'm using the parameters.
This part of the reporting services is so poorly coded, I can't see a single
reason to open the datasets several times, and I see a lot of reasons to
open the dataset only once and then use it for all the parameters, the lists
of available values and the default values.
Thanks,
Daniel Bello Urizarri.

Parameters for Multiple Datasets

I have two datasets. #1 supplies data for table1 and #2 supplies data for table2. They use the same parameters (@.profile_id and @.sort). The first table displays data just fine but the second table never seems to get the parameters and displays nothing. How can I pass these two parameters to the second dataset?

are you sending the same parameters to the 2 datasets

|||

Yes, I am. I discovered late last night that the XML in the RDL file was a little messed up (in the parameters area). After I set the parameters by hand, the report began to behave as I wanted it to.

Parameters changing in PageHeader

Hi Everyone.
I am working on a report that returns information on multiple people.
I would like the name of these people to be shown on every page.
I know we can't have fields in the PageHeader so i have created a Report
Parameter with no prompt and linked it via the default value section on the
report parameter screen to the fullname field in my datasource.
I added a text box which has the assignment of:
=Parameters!FullName.Value
My problem is that for every person returned in my report i have the same
name appearing in the PageHeader. so...
Is there a way to have this parameter dynamically changing depending on what
persons i have in my report? could i have possibly linked the field and the
parameter incorrectly?
--
Thanks in advance,
Dave HuntYou can group your report by =Fields!FullName.Value and place the name field
in the group header or you can place your report into a list box and group
your list by =Fields!FullName.Value and drop the name field in the list area.
U. Tokklas
"DustpanDave" wrote:
> Hi Everyone.
> I am working on a report that returns information on multiple people.
> I would like the name of these people to be shown on every page.
> I know we can't have fields in the PageHeader so i have created a Report
> Parameter with no prompt and linked it via the default value section on the
> report parameter screen to the fullname field in my datasource.
> I added a text box which has the assignment of:
> =Parameters!FullName.Value
> My problem is that for every person returned in my report i have the same
> name appearing in the PageHeader. so...
> Is there a way to have this parameter dynamically changing depending on what
> persons i have in my report? could i have possibly linked the field and the
> parameter incorrectly?
> --
> Thanks in advance,
> Dave Hunt|||In addition to what Tokklas said, you can remove the report parameter for
the FullName. Instead, you will have the FullName placed in a group header
textbox (e.g. with the textbox called "TextboxFullName"). Then in the page
header, you can just another textbox with the following expression:
=ReportItems!TextboxFullName.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tokklas" <Tokklas@.discussions.microsoft.com> wrote in message
news:8BE101C1-F7E7-42CD-B6C2-74203A61CA9D@.microsoft.com...
> You can group your report by =Fields!FullName.Value and place the name
> field
> in the group header or you can place your report into a list box and group
> your list by =Fields!FullName.Value and drop the name field in the list
> area.
>
> --
> U. Tokklas
>
> "DustpanDave" wrote:
>> Hi Everyone.
>> I am working on a report that returns information on multiple people.
>> I would like the name of these people to be shown on every page.
>> I know we can't have fields in the PageHeader so i have created a Report
>> Parameter with no prompt and linked it via the default value section on
>> the
>> report parameter screen to the fullname field in my datasource.
>> I added a text box which has the assignment of:
>> =Parameters!FullName.Value
>> My problem is that for every person returned in my report i have the same
>> name appearing in the PageHeader. so...
>> Is there a way to have this parameter dynamically changing depending on
>> what
>> persons i have in my report? could i have possibly linked the field and
>> the
>> parameter incorrectly?
>> --
>> Thanks in advance,
>> Dave Hunt|||So Guys...
I have a number of tables in a listbox.
Each table has its own datasource.
One of these datasources returns a fullname.
What is the process to assign a group or get an expression into a group
header?
is it a proerty somewhere?
What exactly would i need to do to get my name repeating on each page and
change when a new record starts?
Do you know if in the future it will be posible to have fields in the page
header?
I appreciate your help very much guys! thanks again
--
Thanks in advance,
Dave Hunt
"Robert Bruckner [MSFT]" wrote:
> In addition to what Tokklas said, you can remove the report parameter for
> the FullName. Instead, you will have the FullName placed in a group header
> textbox (e.g. with the textbox called "TextboxFullName"). Then in the page
> header, you can just another textbox with the following expression:
> =ReportItems!TextboxFullName.Value
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Tokklas" <Tokklas@.discussions.microsoft.com> wrote in message
> news:8BE101C1-F7E7-42CD-B6C2-74203A61CA9D@.microsoft.com...
> > You can group your report by =Fields!FullName.Value and place the name
> > field
> > in the group header or you can place your report into a list box and group
> > your list by =Fields!FullName.Value and drop the name field in the list
> > area.
> >
> >
> > --
> > U. Tokklas
> >
> >
> > "DustpanDave" wrote:
> >
> >> Hi Everyone.
> >>
> >> I am working on a report that returns information on multiple people.
> >> I would like the name of these people to be shown on every page.
> >>
> >> I know we can't have fields in the PageHeader so i have created a Report
> >> Parameter with no prompt and linked it via the default value section on
> >> the
> >> report parameter screen to the fullname field in my datasource.
> >> I added a text box which has the assignment of:
> >> =Parameters!FullName.Value
> >>
> >> My problem is that for every person returned in my report i have the same
> >> name appearing in the PageHeader. so...
> >> Is there a way to have this parameter dynamically changing depending on
> >> what
> >> persons i have in my report? could i have possibly linked the field and
> >> the
> >> parameter incorrectly?
> >> --
> >> Thanks in advance,
> >> Dave Hunt
>
>

Parameters - Multiple Values -RS SP2

Hi All.
Just wondering if anyone knows if entering multiple values into parameters
will be included as part of SP2 for reporting services 2000' as it is going
to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
get round it?
--
Nick Colebourn (MCDBA)
DBA
United Coop LtdMulti value parameters will ONLY be in RS 2005
http://www.ReportingServicesFAQ.com/ow.asp?RS2005Features
Nick Colebourn wrote:
> Hi All.
> Just wondering if anyone knows if entering multiple values into parameters
> will be included as part of SP2 for reporting services 2000' as it is going
> to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
> get round it?|||Thanks Jerry. Much Appreciated. Roll On RS 2005! :-)
"Jerry" wrote:
> Multi value parameters will ONLY be in RS 2005
> http://www.ReportingServicesFAQ.com/ow.asp?RS2005Features
>
> Nick Colebourn wrote:
> > Hi All.
> > Just wondering if anyone knows if entering multiple values into parameters
> > will be included as part of SP2 for reporting services 2000' as it is going
> > to be in RS 2005 Beta 3. Or if I'm going to have to code some stored procs to
> > get round it?
>

Parameters - Multiple Values for one Label - Possible?

Is it possible to have a parameter with one label but multiple values. For example:

Label Value

Machinist (100,200,300)

Is it possible to set up an expression that when the user selects this label it will look for the job codes 100, 200 and 300

and return all employees in those codes?

Thanks,

A

No, but what you can do is this...

Create the viewable parameter (We'll call it Parm1)

Create a 2 column SQL table that contains your cross references (We'll call that tblXRef)

in this case tblXRef would contain 3 rows:

Machinist, 100

Machinist, 200

Machinist, 300

Create a dataset that uses the following SQL:

Select Type, ID from tblXRef Where Type = @.Parm1

(Call this dataset Lookups)

Create another parameter (We'll call it Parm2), set it to Internal, Multi value, and set the source = Lookups and the fields = ID

Set the Default Values to Lookups, ID.

In your main SQL dataset, use the Parm2 parameter to do your filtering.

HtH

BobP

|||

Not sure how big is the list but can make a dataset like this and set the paramters -

Select 1,'Machinist-100' As Machinist-100
Union
Select 2,'Machinist-200' As Machinist-200
Union
Select 3,'Machinist-300' As Machinist-300

Hope this helps someway.

Parameterized filters

Hi all;
I know that Parameterized filters allow different partitions of data to be
sent to different Subscribers without requiring multiple publications to be
created (parameterized filters were referred to as dynamic filters in
previous versions of SQL Server). A partition is simply a subset of the rows
in a table; depending on the settings chosen when creating a parameterized
filter, each row in a published table can belong to one partition only
(which produces nonoverlapping partitions) or to two or more partitions
(which produces overlapping partitions).
I need some examples and good resources talking about this.
Any suggestions
Apart from BOL, and the 2 books mentioned in my home page
(www.replicationanswers.com) I don't think you'll find a great deal of
specific material on this. Are there any particular questions you want to ask
about the setup?
Paul Ibison

Parameterized configuratiion in SSIS

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?

For example, one configuration for each country.

Thankful for any help!

//Patrick

Assuming you're kicking off the package with a SQL Agent job, you may find the following code we're using helpful. I have highlighted the section dealing with assigning a configuration file programatically. In your case you can modify the code to point to different configuration files depending on a country parameter.

Hope this helps.

ALTER PROCEDURE [dbo].[UTIL_StartPackageWithDate]

(

@.Packagename VARCHAR(255),

@.InventoryPeriodDate datetime

)

AS

BEGIN

PRINT suser_sname()

DECLARE @.ReturnCode INT

SET @.ReturnCode = 0

DECLARE @.jobId BINARY(16)

DECLARE @.JobName varchar(255)

DECLARE @.cmdline varchar(MAX)

DECLARE @.PackageNameRoot varchar(255)

-- get root of package name

DECLARE @.I int

DECLARE @.X INT

DECLARE @.Done BIT

SET @.Done = 0

SET @.I = 0

WHILE @.Done = 0

BEGIN

SET @.X = CHARINDEX('\',@.PackageName,@.I+1)

IF @.X = 0

BEGIN

SET @.Done = 1

END ELSE BEGIN

SET @.I = @.X

END

END

SET @.PackageNameRoot = SUBSTRING(@.PackageName,@.I+1,LEN(@.PackageName)-@.I)

Print @.PackageNameRoot

SET @.JobName = N'StartPackage_' + @.PackageNameRoot

SET @.cmdline = N'/SQL "' + @.PackageName + '" /SERVER "' + @.@.ServerName + '" /MAXCONCURRENT " 1 " /CHECKPOINTING OFF '

--+ ' /LOGGER "DTS.LogProviderTextFile.1";"packagelog.log" '

--+ ' /CONNECTION "PackageLog.log";"O:\Shared\Logs\' + + @.PackageNameRoot + '.log"'

+ ' /CONFIGFILE "O:\Shared\Configuration\PackageConfig.dtsConfig" '

+ ' /SET "\Package.variables[InventoryPeriodDate]";"' + CONVERT(varchar(10),@.InventoryPeriodDate,120) + '"'

PRINT @.cmdline

IF EXISTS( SELECT * FROM msdb.dbo.sysjobs_view WHERE [name] = @.JobName)

BEGIN

-- exists, so, delete the steps

SELECT @.JobID = job_id

FROM msdb.dbo.sysjobs_view

WHERE [name] = @.JobName

EXECUTE msdb.dbo.sp_update_jobstep

@.job_id = @.JobID,

@.step_id = 1,

@.subsystem=N'SSIS',

@.proxy_name=N'SSISProxy',

@.command = @.cmdLine

END

ELSE

BEGIN

-- Create the job

EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=@.JobName,

@.enabled=1,

@.notify_level_eventlog=0,

@.notify_level_email=0,

@.notify_level_netsend=0,

@.notify_level_page=0,

@.delete_level=0,

@.description=N'THIS JOB IS MAINTAINED AUTOMATICALLY, DO NOT MANUALLY ALTER',

@.category_name=N'[Uncategorized (Local)]',

@.owner_login_name=N'ardent',

@.job_id = @.jobId OUTPUT

-- create the new step

EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'StartPackage',

@.step_id=1,

@.cmdexec_success_code=0,

@.on_success_action=1,

@.on_success_step_id=0,

@.on_fail_action=2,

@.on_fail_step_id=0,

@.retry_attempts=0,

@.retry_interval=0,

@.os_run_priority=0,

@.subsystem=N'SSIS',

@.command=@.cmdLine,

@.proxy_name=N'SSISProxy',

--@.output_file_name=N'C:\datetestlog',

@.flags=0

EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1

EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'

END

EXEC msdb.dbo.sp_start_job @.job_id = @.JobID

END

--select * from msdb.dbo.sysjobs_view

--exec msdb.dbo.sp_help_job @.job_name = 'StartPackage_datetest'

|||

Patrick B wrote:

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package.

Yes, that's possible.

Patrick B wrote:

That you can control via a parameter in some way?

Sorry, not quite sure what you mean by this. Can you explain your scenario?

Patrick B wrote:

For example, one configuration for each country.

Do you mean that you want to selectively choose which configuration to apply? There are ways to do that and Johan alluded to one way of accomplishing it above. Basically instead of defining the configuration files at design-time, you tell the package at execution-time which configuration file to use. I'm guessing that there is more than one way of skinning this particular cat though.

Is this what you mean?

-Jamie