Hello all.
I have built a simple ETL package.
I have an OLE DB source, which then is multicasted. The first output is
pumped into a SQL Server DB and the second one goes into a script destination
which runs an UPDATE on the "Source" table setting a flag field to indicate
that the records have been read.

To do this I have a connection manager which is used for the Ole DB source,
and also referenced in my script component where i specifically define a
connection and open it etc.
Code is below:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim conn As OleDb.OleDbConnection
conn = New
OleDb.OleDbConnection(Me.Connections.Connection.Co nnectionString)

conn.Open()

Dim sqlCmd As New OleDb.OleDbCommand("Update TEST_CHANGES set
CHANGES_EXTRACTED = 'Y' Where CHANGE_ID = " & Row.CHANGEID, conn)

sqlCmd.ExecuteNonQuery()
conn.Close()

End Sub

I have also defined the conection string in an XML configuration file, for
portability reasons.
The problem I have is that although the data is read sucessfully and pumped
into my SQL server Destination, the writeback fails with a blank password
error.

I have read other posts here saying that this can be worked around by using
a config file, but I'm already doing that.
Please help!

Hello JasonB,

Why not use an OLE DB Cmd Transform?

This can exactly what you want.


The OLE DB Command Transformation
(http://www.sqlis.com/default.aspx?309)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks for your help Allan, I'll try that solution.

However this is a proof of concept project and I'm supposed to be learning
the product, along with whatever quirks, as deeply as I can.
Do you have any suggestions as to what can be the root cause of this
particular issue?

"Allan Mitchell" wrote:

Hello Jason,

I suspect this is caused by multicast. If you only use the script
destination with the ole db source, what is the result?

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

Hello privatenews,


Peter. I am interested in why the Multicast would have this effect on the
pipeline. Why would the destination not be affected with a straight 1:1
Can you elaborate please? The multicast does not reshape anything, in fact
the buffer is the exact same buffer flowing down the paths.

Curious.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello Allan,

I agree with you the buffer is the same. I just want to check if the update
to the source table conflicts with read for this OLE DB data source.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

Sorry about the Delay in getting back to you.
I've run the Package without the Multicast, Just using an OLE DB source and
the script destination. Again the password error occurred.

In an effort to debug this I added some extra script that writes out the
connection string to a text file, so that it can be examined.
The Password is not being picked up from the configuration file! Is there
some sort of code you have to execute to tell the application to read the
config file?

I have included the Password in the "Connectionstring" property and also the
"Password" property, but for some reason it is still being dropped.



""privatenews"" wrote:

Hello Jason,

You may try to use the connection string directly in connection to test. I
use the following script and it works fine.


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here

Dim conn As OleDb.OleDbConnection
conn = New OleDb.OleDbConnection("Data Source=.;Initial
Catalog=Northwind;Provider=SQLOLEDB.1;User ID=sa;Password=Password")

conn.Open()

Dim sqlCmd As New OleDb.OleDbCommand("update products set
ProductName='testChai' where ProductID=1", conn)

sqlCmd.ExecuteNonQuery()
conn.Close()
'
End Sub


As for connection string issue, you may want to post in .net newsgroup so
that you could get more qualified answers. The following link is for your
reference:
Custom Configuration SQL Connection String Section
http://www.codeproject.com/dotnet/Cu...figSqlConn.asp


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===

This posting is provided "AS IS" with no warranties, and confers no rights.