How to fix DT_Text and DT_NText Read Only in Script Component

I have a script component that I have written and works as long as the Output columns on the script are string types. When I change the output column type to text (since the size could be essentially unlimited) it gives an error in the script component that the property is read only.

Here is the code line that fails with Property Payments is read only.

Output0Buffer.Payments = fieldValues(i)

If I change the column payments to DT_Wstr it works without issue, but I want to use text incase the value is large.

Here is the error if you try to run the actual script even though I know it has an error.

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [DTS.Pipeline]: "component "Script Component" (85)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Try explicitly calling SetString() on the column. Any better?

Thanks
Mark

|||First things first, make sure the input column usage type is set to Read/Write.

The reason it doesn't work is that Blob data types have a different interface in pipline script components.

Then, to set to value to a text field (DT_TEXT or DT_NTEXT) in a pipeline script component, use AddBlobData(), as in:

Imports System.Text
...

Output0Buffer.Payments.AddBlobData(Encoding.Unicode.GetBytes(SomeStringHere))|||I had just figured it out before this post.. but my code was way worse... yours works well and is clean. Thanks!

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||This is a multi-part message in MIME format.
--010807000500020704010007
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:
>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
--010807000500020704010007
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Depends on what info you want to find out. But it will be some
variation on this:<br>
</tt>
<blockquote><tt>select object_name(i.[id]) as tablename, i.*</tt><br>
<tt>from dbo.sysindexes as i</tt><br>
<tt> inner join dbo.sysfilegroups as g on g.groupid = i.groupid</tt><br>
<tt>where g.groupname = 'PRIMARY'<br>
</tt></blockquote>
<tt>If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Anubis wrote:
<blockquote cite="miduyBFlhIcFHA.1148@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
</pre>
</blockquote>
</body>
</html>
--010807000500020704010007--|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.
sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>
|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])

How to filter with hierarchy

Hi,

I have a cube which has a "Relationship Hierarchy" dimension which is hierarchical. I have written the following MDX query which has a filter on the rows axis...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
FILTER(
Hierarchize({[Relationship Hierarchy].[Level1 Full Name].members}),
[Relationship Hierarchy].currentmember is [AXA SA]]
)
}
ON ROWS
FROM [MyMart] WHERE ([Measures].[Costs])

The query works as expected, but now I want to do something a little more advanced and I can't figure out how. I would like to have all children of 'AXA SA' returned too. So basically, I want to say this...

FILTER(<set>, [Relationship Hierarchy].currentmember is [AXA SA] or [Relationship Hierarchy].currentmember is [AXA SA].children)

Obviously the "is [AXA SA].children" syntax is not valid. But does anyone know how to do this?

Similarly, is possible to say I want any members from "[Relationship Hierarchy]" who have a parent of "[AXA SA]"?

Thanks,

Lachlan

In your example, the Filter function is redundant. You can simply use { [AXA SA] } or [AXA SA].children to get the sets you want.|||

Hi,

Sorry, maybe my question didn't really make sense. What I wanted to do was only filter on the level 1 members in my [Relationship Hierarchy] dimension. The problem with my query above is that the children are never retrieved because the filter is also applied to them too. I just needed to move the filter to the correct place so the children are not filtered too...

SELECT
NON EMPTY { Hierarchize({[Cost Centre].[L01 Cost Centre Name].members}) } ON COLUMNS,
NON EMPTY {
Hierarchize({ FILTER([Relationship Hierarchy].[Level1 Full Name].members, [Relationship Hierarchy].currentmember is [AXA SA]), AddCalculatedMembers( {[Relationship Hierarchy].[All Relationship Hierarchy].[AXA SA].children} )})
}
ON ROWS
FROM [mymart]
WHERE ([Measures].[Costs])