Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Monday, March 26, 2012

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
Jeff
Hi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegrou ps.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>
sql

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
JeffHi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegroups.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
JeffHi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegroups.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>

Monday, March 19, 2012

how to add rows with Script Component?

Why am I getting this design-time error from my script:

'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'

Here's my script:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim output_row As Buffer = Row

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

output_row = Row.AddRow()

End If

output_row.seq = seq

output_row.value = code

seq += 1

Next

End Sub

End Class

And here's the error it generates:

Error 3 Validation error. Convert Bib (ISC) to Prep Item_Detail: Split ISC codes [52]: Error 30311: Value of type 'ScriptComponent_d021a3fd485946868f5f0daadaf0e57c.Input0Buffer' cannot be converted to 'System.Buffer'. Line 15 Column 36 through 38 Error 30390: 'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'. Line 19 Column 30 through 39 Error 30456: 'seq' is not a member of 'System.Buffer'. Line 21 Column 13 through 26 Error 30456: 'value' is not a member of 'System.Buffer'. Line 22 Column 13 through 28 ConvertDIN.dtsx 0 0

You are getting the error because your trying to get async script behavior from a synchronous script. This is clear from the for loop per input row, which could produce multiple output rows per input row. This is practically the definition of an asynchronous component.

So, make your component asynchronous.

1. On your output, set the SynchronousInputID to 0. Now you have an async component.
2. Add columns to your output, like seq and value.
3. Add rows to the output, as in the following.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

Output0Buffer.AddRow()
Output0Buffer.seq = seq
Output0Buffer.value = code
End If
seq += 1
Next
End Sub|||

Thanks, now it compiles! But how does the object returned by Output0Buffer.AddRow() get its seq and code properties set? When I ran the above code, I got 0 output rows.

Also, how do I output a row when seq = 0, that is, the output row that corresponds to the first code in the input Row.IC string?

|||

I took out the test for seq > 0, and replaced the Recordset destination I was using for debugging with an OLE destination, and now my data view shows 1 row being output for each input row. Thanks!