|
|
You're in the
sql server dts
group:Connection string passwords
sql server dts:
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.ConnectionString) 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.
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 [quoted text, click to view] > 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.ConnectionString) > 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! >
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? [quoted text, click to view] "Allan Mitchell" wrote:
> 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 > > > 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.ConnectionString) > > 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 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 [quoted text, click to view] > 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 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 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. [quoted text, click to view] ""privatenews"" wrote:
> 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. > >
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/CustomConfigSqlConn.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.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |