ProcessRowInput not containing any Row data

I have a script component as a destination. I believe I'm doing something very simple, but for some reason, I'm not getting back any data.

When I just msgbox the data in Row.Address, I get nothing. If I msgbox Row.Address.toString, I get "Object reference not set to instance of an object."

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
MessageBox.Show(Row.Address)
End Sub

I have Address as an input and output column in my script component setup. Any help? It seems like it's gotta be something simple, but I'm pulling my (little bit of) hair out to find it.

mmmm... think my problem is elsewhere. thanks anyways!

Processing XML data from a table

We have an XML column in a SQL Server 2005 table. Each row of this table contains one XML document.

I want to shred values from the XML documents and process these within a Data Flow. I want the Data Flow to execute once across a record set comprised of all of the XML documents.

I can shred the XML using a For-Each loop and XML Task. I'm kinda stuck on how I then get the data from variables into a Recordset or similar so that I can process this within single iteration of a Data Flow.

Or - is my approach incorrect? I seem to be building a verbose and clunky solution to this problem. I know I could accomplish the same in a pretty simple SQL statement using .value on the XML column... am I missing something? Is a SQL query just better suited to this problem?

Any help much appreciated.

James

" how I then get the data from variables into a Recordset" ... the script source component.

Processing one row at a time

I'm populating a new table based on information in an existing table. The new table is a list of all "items" and contains a primary key. The old table is a database of receipts where items can appear many times in any order.

I have put together the off-the-shelf components to do this, using a lookup transformation to see if the item is already in the new table. Problem is, because there's so much repetition in the old table I need to process the old table one row at a time. Batch processing is generating errors because the lookup doesn't detect duplicates within the buffer.

I tried setting the "DefaultBufferMaxRows" property of the task to 1, but that doesn't seem to have any effect.

To get the data from the old table, I'm using an OLE DB source. To get the data into the new table, I'm using the OLE DB Command transformation with parameters to execute an INSERT statement.

This is a job I have to do exactly once, so I don't care if I have to run it overnight. I'd rather have a simple, easy to understand but inefficient script so I understand what it's doing completely.

Any help on forcing SSIS to process one row at a time?

Lee Silverman
JackRabbit Sports

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?|||

Could you use an Aggregate transform to eliminate the duplicates, or a GROUP BY in the source select?

Unfortunately no. Items wwith the same unique identifier have often had important changes made to the descriptive fields over the course of the last two or three years. When the Lookup finds a unique identifier that is already in the table, the branch that handles that then goes on to check to see if the descriptive info has changed, and if so updates the data in the table. Likewise, on the side of the lookup where the unique identifier was not found, that branch needs to see handle the case where the new item shares an item lookup number with a previous item, and if so marks the previous item as expired.

The data in the source table is ordered sequentially, and the logic of the script requires that each line item be processed one at a time. It's going to be slow, but once it's done it'll be done.

Lee Silverman
JackRabbit Sports

|||Could you use a For Each Container with the ADO enumerator to loop through your recordset of tableA and then do your manipulations/lookups within that?|||You could use an OLE DB Command to do your inserts and updates, and disable caching on the lookups. That will force the lookups to requery the database for each row. I'm not sure that will work in your case, but it might be worth a try.|||

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

In any event, I solved the problem by writing a script in T-SQL, and I took the opportunity to teach myself how to use a cursor to scroll through the original table one line at a time. It's running now.

Lee

|||

Lee Silverman wrote:

How does one disable caching on lookups? Do you have to check "enable memory restriction" but then not check "enable caching"?

That is it.

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

Processing dataset as a whole, and not per row

Which component should be used to process dataset as a whole, and not on per row basis? I have need to process dataset conditionally (condition based on dataset), e.g. if a special row is present in dataset than dataset should be processed in a special way. Should I maybe use one Script Transformation to determine if dataset satisfies condition (and store that result into a variable), and then based on that condition (variable value) perform or not processing (using Conditional Split and Script Transformation)?

You could try to identify if the 'special row' exists through a sql statement in an execute sql task, then set a variable to '1' or '2' and then have precendent constraints to two different data flow tasks evaluated as expressions based on the value of the variable from the execute sql task. Hope that makes sense, I realize it isn't all that clear.

|||Yes, it makes sense, thanks. With that said, I have a new problem - have to find out how dataset/context is passed between different data flow tasks of a single control flow (using DataReader Source and Destination maybe?). (Did I mention I was a beginner...well I am)

Another problem I have to solve is to implement specific transformation. Within a dataset there are two distinct groups of rows - distinction is based on value of a single column. Transformation should for each row of one group, lookup matching row in other group. If matching row is found than based on its column values, column values of a row from first group should be updated. Otherwise, an additional row should be appended to the dataset with (almost all) column values just copied from first group row. How does one do something like that - looping with inner looping, and appending rows to a dataset?

|||

You can use RAW files to persist the data between data flow tasks. They are extremely fast. The issue with using the recordset is that you have to use a Script Source to process it in the second data flow (the DataReader source uses an ADO.NET connection to a database to retrieve data.)

On your second problem, you could look at using a conditional split to split the groups, then a Merge Join to join them back together based on the matching columns. For adding the additional rows, you'd need to use a script component. There are probably other ways to do this, that's just the first to come to mind.

|||I believe exporting data to files will present a security issue. Maybe I could do everything just on Data Flow - just use single script component to determine whether processing is needed and store it to variable, and then use Split to direct whole dataset either to be processed branch or to the other branch where no processing will be done. Question is how fast would this solution perform.

For second problem, I still don't see how splitting dataset would solve my problem - I don't know how could I reference second part of the dataset when going through first one. For each row in first dataset a matchig row has to be found in second dataset. If found, row in first dataset gets updated (using values from second group). If not found, new row in first group has to be appended. Maybe Lookup component could be used for this - default output when matching column is found, otherwise use error output.

In this short time I couldn't find the way to append a row using Script Transformation component. Could you please give me a reference to a book, web site, or something similar where this information can be found?
|||

Stevo Slavic wrote:

I believe exporting data to files will present a security issue.

Perhaps, but the files aren't in plain text. You can also use staging tables in a database instead of using files.|||

Stevo Slavic wrote:

In this short time I couldn't find the way to append a row using Script Transformation component. Could you please give me a reference to a book, web site, or something similar where this information can be found?

You need to use an async output on the script. See http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx for an example of an async component. It's also documented in Books Online: http://msdn2.microsoft.com/zh-cn/library/ms136133.aspx

Processing dataset as a whole, and not per row

Which component should be used to process dataset as a whole, and not on per row basis? I have need to process dataset conditionally (condition based on dataset), e.g. if a special row is present in dataset than dataset should be processed in a special way. Should I maybe use one Script Transformation to determine if dataset satisfies condition (and store that result into a variable), and then based on that condition (variable value) perform or not processing (using Conditional Split and Script Transformation)?

You could try to identify if the 'special row' exists through a sql statement in an execute sql task, then set a variable to '1' or '2' and then have precendent constraints to two different data flow tasks evaluated as expressions based on the value of the variable from the execute sql task. Hope that makes sense, I realize it isn't all that clear.

|||Yes, it makes sense, thanks. With that said, I have a new problem - have to find out how dataset/context is passed between different data flow tasks of a single control flow (using DataReader Source and Destination maybe?). (Did I mention I was a beginner...well I am)

Another problem I have to solve is to implement specific transformation. Within a dataset there are two distinct groups of rows - distinction is based on value of a single column. Transformation should for each row of one group, lookup matching row in other group. If matching row is found than based on its column values, column values of a row from first group should be updated. Otherwise, an additional row should be appended to the dataset with (almost all) column values just copied from first group row. How does one do something like that - looping with inner looping, and appending rows to a dataset?

|||

You can use RAW files to persist the data between data flow tasks. They are extremely fast. The issue with using the recordset is that you have to use a Script Source to process it in the second data flow (the DataReader source uses an ADO.NET connection to a database to retrieve data.)

On your second problem, you could look at using a conditional split to split the groups, then a Merge Join to join them back together based on the matching columns. For adding the additional rows, you'd need to use a script component. There are probably other ways to do this, that's just the first to come to mind.

|||I believe exporting data to files will present a security issue. Maybe I could do everything just on Data Flow - just use single script component to determine whether processing is needed and store it to variable, and then use Split to direct whole dataset either to be processed branch or to the other branch where no processing will be done. Question is how fast would this solution perform.

For second problem, I still don't see how splitting dataset would solve my problem - I don't know how could I reference second part of the dataset when going through first one. For each row in first dataset a matchig row has to be found in second dataset. If found, row in first dataset gets updated (using values from second group). If not found, new row in first group has to be appended. Maybe Lookup component could be used for this - default output when matching column is found, otherwise use error output.

In this short time I couldn't find the way to append a row using Script Transformation component. Could you please give me a reference to a book, web site, or something similar where this information can be found?
|||

Stevo Slavic wrote:

I believe exporting data to files will present a security issue.

Perhaps, but the files aren't in plain text. You can also use staging tables in a database instead of using files.|||

Stevo Slavic wrote:

In this short time I couldn't find the way to append a row using Script Transformation component. Could you please give me a reference to a book, web site, or something similar where this information can be found?

You need to use an async output on the script. See http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx for an example of an async component. It's also documented in Books Online: http://msdn2.microsoft.com/zh-cn/library/ms136133.aspx

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

process all row

how would i do this in pure t-sql not in a win app
i have column A B and C all integers
psuedo sql
INSERT INTO TABLE1 (C) values (A+B)
A+B = C
thanksHoward
CREATE TABLE #Test
(
A INT,
B INT,
C AS COALESCE(A,0)+COALESCE(B,0)
)
INSERT INTO #Test SELECT 1,2
SELECT * FROM #Test
"Howard" <howdy0909@.yahoo.com> wrote in message
news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
> how would i do this in pure t-sql not in a win app
> i have column A B and C all integers
> psuedo sql
> INSERT INTO TABLE1 (C) values (A+B)
> A+B = C
> thanks
>|||Exactly as you stated
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Howard" <howdy0909@.yahoo.com> wrote in message
news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
> how would i do this in pure t-sql not in a win app
> i have column A B and C all integers
> psuedo sql
> INSERT INTO TABLE1 (C) values (A+B)
> A+B = C
> thanks
>|||oops sorry i meant update
UPDATE table1
SET colC = colA + colB
WHERE loop all
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:LfWdncfWt9dmaLXZRVnyvQ@.bt.com...
> Exactly as you stated
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:u3lkECkUGHA.776@.TK2MSFTNGP09.phx.gbl...
>|||An unrestricted update (no WHERE clause) will process the entire table.
E.g.:
UPDATE table1
SET colC = colA + colB
ML
http://milambda.blogspot.com/|||Also you dont need to have other column. Just use select
Select colA, colB, colA+colB as colC from table
Madhivanan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

Process Add by AMO

Hi,

I want to write a programm which adds entries to an sql table and then process the dimension with processadd to only add this single row.

I have checked the samples and internet forums - but could not find out howto do..

anyhow I miss the link between a querybinding and the process command of the dimension...

I thought it would be something like...

Code Snippet

/* connect to lap server */

olapClient.Connect("DataSource=" + OLAPServerName);

/* get the Database */

Database olapDB = olapClient.Databases.GetByName(DatabaseName);

/* get the Dimension */

Dimension olapDim = olapDB.Dimensions.GetByName(DimensionName);

/* process the dimension and add the items i have created before */

olapDim.Process(ProcessType.ProcessAdd, new QueryBinding(DataSourceName, "select * from dbo.Slot where Slot='" + ResultID + "'"));

but this only raises an error ("

Errors in the metadata manager. The object reference is not valid. It does not match the structure of the metadata class hierarchy.")

How is this done correctly?

I want to use AMO to not write the xmla command statically - if there are further changes - but the dimension is build up to 100% of the single table dbo.Slot.

An example or a link would be helpfull.

HANNES

Nobody an idea?|||

Check this out. I think this guy has worked out the issues:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx

|||

I have seen this. My Question is - how do I programmatically create the xmla command. It seems AMO always creates wrong commands - the command always looks like a Process ADD for a partition - even if we do it on the dimension object.

It you confirm that there is no other way I will go by manually write xmla. But the drawback is - if we change the dimension I have to manually change the XMLA - and parts of the code - and otherwise the would be much less maintenance.

Hannes

|||

When I review the blog, it appears he works out both techniques. In his first example, he shows how to use query bindings which works for the Partition version. Towards the bottom, he shows you that you must create a DSV in AMO to provide the data source for the Dimension verions. (The DSV is used instead of the query bindings.)

I've looked through the Melomed et al. (SAMS) and Lachev (PROLOGIKA) books and do not find any additional info that would be of help to you. In the Harinath and Quinn book (WROX), an XMLA example of the process add against a dimension is shown on page 443. It too shows a DSV being generated to hold the query for the process add on the dimension.

Good luck,
Bryan

procedure to retrieve primary key

Hello!
I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.check out SCOPE_IDENTITY() in bol

EDIT: i am assuming you are using an identity column for your pk. if you aren't, then you must already know the value you are inserting, right?|||That's exactly what I was looking for. Thanks.