I am modifying rows of table via a script:

OLEDB source --> Script Component --> OLE DB Destination (same as source)

I am getting correct functionalty. My problem is that the transformed rows
are appended to the original table.

Desired functionality is to just modify the rows, not append the modified
rows.

How may I modify rows without appending fresh copies of modified rows?

Thanks much!

Shakir

Hi Shakir,

Do you use SQL 2000 ActiveX scripts DTS or SQL 2005 SSIS script task?

If you are using SQL 2000 ActiveX scripts DTS, by default, DTS
import/export wizard doesn't provide update option for data transformation.
To realize such function, you need to use ADO Recordset in your ActiveX
scripts.

For how to use ADO Recordset in ActiveX scripts, you can refer to:
Using ActiveX Scripts in DTS
http://msdn.microsoft.com/library/de...us/dtssql/dts_
addf_ax_06er.asp

If you are using SQL 2005 SSIS script task, you still have to use sql
command to do the job other than use oledb destination directly.
You may refer to:
http://blogs.conchango.com/jamiethom...02/08/960.aspx

If you have any other questions or concerns, please feel free to let me
know. it's my pleasure to be of assistance.

Charles,

Thanks. I looked at the article, and the newer one it referred to. I am
using SSIS 2005.

Jamies blog talked about recorsets and raw files, and I don't know how they
are directly relevant to my case. My current workaround is:
1 mark the original records,
2 run the script component: adds modified records to table
3 delete the original records (based on the mark)

How may I use an SQL statment as you mentioned in your post? Eg.

MyTable (a, f(a), g(a))
f(a) and g(a) are calculated in the script component. What/where can an SQL
statement perform this update?

Shakir

"Charles Wang[MSFT]" wrote:

Hi Shakir,
Thanks for your post.

You need to program by ADO.Net in your script component. Create SqlCommands
for query,inserting, updating and deleting.
If you are not familiar with ADO.NET, I recommend that you consult a .NET
professional on how to use ADO.NET in VB.NET.
Here is a reference from MSDN:
Creating a Destination with the Script Component
http://msdn2.microsoft.com/en-us/library/ms135939.aspx


If you have any other questions or concerns, please feel free to let me
know. I would like for further assistance.

Thanks, I will look at the ADO.NET destination example in the link you
provided.

I do wonder why a such simple task would require that much complexity.

Shakir

"Charles Wang[MSFT]" wrote:

Hi Shakir,
Thanks for your response.

I think it should be by design. SSIS need to consider various database
providers and abstract some interfaces for data sources. Also, script
component is a developer related feature and designed for those people who
need to program to realize their advanced or custom needs.

If you don't use SSIS, you can use the SQL statement OPENROWSET to
transfer data between two different data sources.

If two data sources are both SQL Server, you can use SQL directly by adding
linked server.

You may want the references:
OPENROWSET (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190312.aspx

sp_addlinkedserver
http://msdn2.microsoft.com/en-us/library/ms190479.aspx

If you have any other questions or concerns, please don't hesitate to let
me know.

Charles Wang
Microsoft Online Community Support

Charles, thanks. This is interesting information that I could probably put
to sometime in the future.

However, just now, I am using many functionaties from VB to transform data,
moving around data, sending emails etc. all in an offline, scheduled fashion.
So I think that SSIS would be the way to go. I think I'll further explore
ADO.NET per your earlier suggestion.

I feel that there must be a simple way to transform (in SSIS + script) rows
in a local db. In client-server or distributed db's etc, I'd probably need
to use ADO.NET. As is, I have the final processed data in my script output,
I can see it in the data viewer, I can even append these rows to my table.
But... I don't know how to overwrite my table with these records (ie update).

Thanks

Shakir

"Charles Wang[MSFT]" wrote:

Hi Sharkir,
Thanks for your response.

Because of a bulk update, I recommend that you use a select command to
query the data source and fill the data into a dataset or datatable and
then loop fetch each row from the datatable and then use a update command
to update the row to the destination. I can give you some general
suggestions, but please understand that I'm not an expert on ADO.NET.
Here is a simple example (C#):
DataSet ds = new DataSet();
SqlDataAdapter adpSrc = new SqlDataAdapter();
adpSrc.SelectCommand = new SqlCommand("select * from
...",connection_source);
adpSrc.Fill(ds);

SqlDataAdapter adpDest = new SqlDataAdapter();
SqlCommand updateCmd = new SqlCommand("update ... set field1=@field1,
field2=@field2 where ID=@ID",connection_destination);
adpDest.UpdateCommand = updateCmd;
updateCmd.Parameters.Add("field1","SqlDbType.varch ar",20,"@field1");
updateCmd.Parameters.Add("field2","SqlDbType.int", 8,"@field2");
updateCmd.Parameters.Add("ID","SqlDbType.Int",8,"@ ID");

If(ds.Tables.Count>0)
{
ds.Tables[0].Name = "database table name";
adpDest.Update(ds.Tables[0]);
}

In fact, there are also many other ways to access SQL Server by ADO.NET.
You can also loop update the rows by the query result set. I recommend that
you borrow a book regarding ADO.NET. It will lead you to master this
technic quickly.


Charles Wang
Microsoft Online Community Support

Charles,

I am thinking along similar lines. I would prefer to do all of my
processing from within SSIS. So I this is what I am thinking of doing (at a
high level):

1. Read data from an OLE DB Source into a Recordset Destination
2. Use a Foreach loop on the recordset with a Script task
3. Finally, read back the recordset into the OLE DB Source

I'll probably post again if I get stuck with something.

Shakir

"Charles Wang[MSFT]" wrote:

Hi Shakir,
Thanks for your response.

Don't hesitate to let me know if you have any questions on this issue.

Have a good day!

Charles Wang
Microsoft Online Community Support