Accessing DataReader data in Script Task

I have a Data Flow task that sends its results to a DataReader destination. My Data Flow is then connected to a Script Task. I'm trying to figure out how to access the data stored in that DataReader from my Script Task, but having no luck. Any help would be greatly appreciated.

OK, I've figured out how to do this, but using a Recordset, not a Datareader.

1. Pass whatever data you want to process in your Data Flow to a Recordset destination, and store it in an SSIS variable. In my case, the variable is called "ConsumerIDSet", and it's storing only one row called "ConsumerID".

2. In the Control Flow, add a Script Task. Connect the output of your Data Flow to the input of the Script Task.

3. Make sure the variable you're storing the Recordset into is availabe to the Script Task (add it to the ReadOnlyVariables list).

4. Open the VSA script designer, and make sure to add a reference to ADODB (Project...Add Reference...select ADODB).

Here's the script:

Public Sub Main()
Dim dbConnect As New OleDbConnection("[connect string]")
Dim dbCommand As New OleDbCommand
Dim consumerID As String
Dim rs As ADODB.Recordset

rs = CType(Dts.Variables("ConsumerIDSet").Value, ADODB.Recordset)

Try
dbConnect.Open()

Do While Not rs.EOF
consumerID = rs("ConsumerID").Value.ToString

dbCommand.CommandText = _
"UPDATE tblname SET extracted = 1 WHERE consumerid = " + consumerID
dbCommand.Connection = dbConnect
dbCommand.ExecuteNonQuery()

rs.MoveNext()
Loop

Dts.TaskResult = Dts.Results.Success

Catch
Dts.TaskResult = Dts.Results.Failure

Finally
dbConnect.Close()

End Try
End Sub

I'm not an experienced developer, so that might not be the best way to do it, but it works. BTW, I'm connecting to an Oracle database which is why I'm using OleDbConnection, so you'll have to adjust your connection type.

I'm still curious as to whether this can be done with a DataReader, so if you have any suggestions, let me know!

|||You might try cracking it with a ForEach loop.
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
K|||The DataReader Destination is designed to expose the result of the whole package as data source to some external (to the package) application, say Reporting Services, or your custom application. It is not really designed for reading data from inside of the package itself.

As you've found out the RecordSet Destination is designed exactly for your goal - for saving the data into record set that you can then use inside the same package. In addition, as Kirk wrote, we provide other ways to interact with data collected by RecordSet Destination, e.g. for each loop can iterate over it.

If you explain the bigger picture, we might be able to provide better suggestion on the design of the package.|||I was using the ForEach loop, and running the script task inside that. But that meant opening and closing a database connection on each iteration of the loop. It makes more sense to me to do the loop inside of the script itself.

Regarding the bigger picture...I've been asked to dig in to SSIS as a possible replacement for our current ETL tool, which is closed, proprietary, and expensive. We do quite a bit of interaction with Oracle, mostly involving extracting and formatting consumer data. My job is to learn SSIS quickly so we can figure out how best to apply it to our current processes. It's a bit early to say exactly what I'm trying to accomplish--I'm not sure yet! As I learn it better and figure out how we're going to use it I may have more specific scenarios.

Thank you for the help!|||Let us know how we can help.
K

accessing a remote Windows database

I'm trying to figure out how to access a Windows database (SQL server) from a Linux server. I'm trying to use Win32::ODBC and I've got a lot of information on how to make a connection, running queries, extracting the data, but the piece that I'm missing is what do you do if your database in NOT local ?? I'm assuming accessing remote databases is something developers do, so I'm a little confused on why I can't find any data about this. Can you provide any information ?In the strictly ADO microsoft world, you can supply a Data Source parameter on your connection string. The data source would be the name or IP (hopeflully name) of the server the database lives on.

Also, you may have better luck in trying to get the DBI's/DBM's for perl and SQL Server.|||I'm not very familar with the windows world (I'm in the Linux/Unix) ... can you possibly give some examples/references of what you mean ? Basically, someplace where I can see some sample code

Thanks !!|||If you have a connection string that you are passing to the connection object, it would look like this in Windows:

"DSN=SomeDSNName;UID=UserName;Password=Something"

The ODBC DSN (Data Source Name) will direct the request to the correct driver, server, and in some cases the database as well. If you do not have a DSN available, you have to supply these yourself:

"Provider=SQLOLEDB;UID=UserName;Password=something; Data Source=ServerName;Database=DatabaseName"

As for examples from a UNIX/Linux world, I am at a total loss. I was not even sure that ODBC existed on Linux. Hope this helps.|||I think I need to use the DBI::Proxy, I just have to make sure the correct modules are loaded on the windows machine

How do I access a MS Windows based database from UNIX/Linux using DBI::Proxy?
On the UNIX/Linux machine install DBI and all the modules to support DBD::Proxy.
On the Windows system install DBI, all the modules for DBD::Proxy, and DBD::ODBC.
Define a system DSN (using ODBC connection manager) to the Windows database.
Using the ODBC Test connection, test the ODBC connection.

I just know much about loading modules on Windows. Any thoughts on that ??

Thanks for your help so far.

Access SubQuery Help Needed.....................

Don't know if this is the proper newsgroup to post to. Sorry if not.
Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).
I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.
From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, .... I will need a few more
columns from each of the tables for the final report.
I've tried many variations of the Select statement without success.
Such as:
Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber
Looking for a solution,
HexmanFROM using subquery not a table(s) is not supported in SQL92.
<code lang="SQL" type="AirCode">
SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
FROM IMast i INNER JOIN THist p ON i.part = p.wopart
WHERE i.pcode = "CAST"
AND ((thisdate - wodate) < 91)
</code>
Regards John
"Hexman" <Hexman@.binary.com> wrote in message
news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
4ax.com...
> Don't know if this is the proper newsgroup to post to. Sorry if not.
> Can't figure this one out. Using Access 2003. I have 2 tables, IMast
> (table of part info) and THist (part transaction history).
> I need to select all parts that have a product code = "CAST" and list
> the last 10 history transaction if they are within 90 days of the run
> date.
> From the IMast table I need the columns: part, descr, pcode. From the
> THist I need: wonumber, wodate, wocust, .... I will need a few more
> columns from each of the tables for the final report.
> I've tried many variations of the Select statement without success.
> Such as:
> Select part, desc, pcode
> from (Select top 10 wonumber, wodate, wocust
> from THist
> where (pcode = "CAST") and (part=wopart) and ((thisdate -
> wodate) < 91)
> order by part, wodate, wonumber
> Looking for a solution,
> Hexman
>|||John,
An Order By clause is also needed, so the Top 10 will return the appropriate
data.
Kerry Moorman
"John Griffiths" wrote:

> FROM using subquery not a table(s) is not supported in SQL92.
> <code lang="SQL" type="AirCode">
> SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
> FROM IMast i INNER JOIN THist p ON i.part = p.wopart
> WHERE i.pcode = "CAST"
> AND ((thisdate - wodate) < 91)
> </code>
> Regards John
> "Hexman" <Hexman@.binary.com> wrote in message
> news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
4ax.com...
>
>

Access SubQuery Help Needed.....................

Don't know if this is the proper newsgroup to post to. Sorry if not.
Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).
I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.
From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, .... I will need a few more
columns from each of the tables for the final report.
I've tried many variations of the Select statement without success.
Such as:
Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber
Looking for a solution,
HexmanHexman,
What are the common columns for the 2 tables? If you are trying to get join
the 2 tables, you have a use a join statement such as this. Then put your
condiction in the where column.
SELECT [Orders].[OrderID], [Orders].[CustomerID], [Orders].[EmployeeID] FROM
Employees INNER JOIN Orders ON [Employees].[EmployeeID]
=[Orders].[EmployeeID];
Sam
"Hexman" wrote:

> Don't know if this is the proper newsgroup to post to. Sorry if not.
> Can't figure this one out. Using Access 2003. I have 2 tables, IMast
> (table of part info) and THist (part transaction history).
> I need to select all parts that have a product code = "CAST" and list
> the last 10 history transaction if they are within 90 days of the run
> date.
> From the IMast table I need the columns: part, descr, pcode. From the
> THist I need: wonumber, wodate, wocust, .... I will need a few more
> columns from each of the tables for the final report.
> I've tried many variations of the Select statement without success.
> Such as:
> Select part, desc, pcode
> from (Select top 10 wonumber, wodate, wocust
> from THist
> where (pcode = "CAST") and (part=wopart) and ((thisdate -
> wodate) < 91)
> order by part, wodate, wonumber
> Looking for a solution,
> Hexman
>

Access Locking problem

I've been working on this problem for days and cannot seem to figure it out. I'm using the Microsoft Access Driver in an ASP web page to try to update a database. I'm using adLockPessimistic, but it is acting like I'm using adLockOptimistic.

Before you ask, I'm using MDAC 2.8 SP2 on my Windows 2003 Server.

If I open two web pages at the same time, both trying to increment a counter in a database 100 times, the end result should be a value of 200. But in most trials, I get a number less than 200, like 187. It almost seems like it is not locked at all, ecept for the fact that I sometimes get an error telling me I can't update the data because it is locked by another user.

So... I can read OK, but not save because sometimes its locked by another user and sometimes the increment doesn't really increment, therefore it seems like I have adLockOptimistic instead of adLockPessimistic. I do get adLockPessimistic when I read back the lock type.

Any help would be great!

Code Snippet

for i = 1 to 100
if ( len(Request.Form("zipcode")) > 0 ) then
call LogImpression (38)
end if
next

Function LogImpression (CampID)
'This increments the impression counter for a campaign in the vendors database
On Error Resume Next

Set adoCon = Server.CreateObject("ADODB.Connection")
'adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("accessdb/vendors.mdb")
adoCon.Open "DSN=vendorDB"
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT vendors.impressions,vendors.ID FROM vendors WHERE ID=" & CampID & ";"
rsAddComments.CursorType = 2
rsAddComments.LockType = 2
rsAddComments.CursorLocation = 2

rsAddComments.Open strSQL, adoCon, 2, 2, adCmdTableDirect
if (NOT rsAddComments.EOF) then
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount
rsAddComments.Update

if Err.Number <> 0 then
'we had an error, row was locked
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount

rsAddComments.Update
end if

end if
rsAddComments.Close

Set rsAddComments = Nothing
Set adoCon = Nothing
end function

Thanks, Lee

Your code itself doesn't lock this critical section. You cannot consider these two statements to be atomic. You have two database operations going on, and you are trying to run them atomically, so your code needs to lock around that area or you need to wrap the commands in transactions.

Here is the scenario you have right now:

Assume that the database starts with "impressions" set to 1:

process A updates MyCount = impressions + 1 = 2 then context switches out

process B updates MyCount = impressions + 1 = 2 then context switches out

process A sets impressions = MyCount = 2 then context switches out

process B sets impressions = MyCount = 2 then context switches out

These concurrency issues do not happen all the time, and are hard to reproduce, but this is clearly an application level concurrency issue.

I suggest wrapping those two lines in a critical section to enforce serialization there.

Hope that helps,

John

Access Last Identity Key

Hi am going around in circles on this and can't figure it out ... I imagine the answer is simple.

I have a data flow which results in the addition of a record to a table.This table has an identity key.

I now wish to use this identity key to create records in other tables. So I am doing this in another data flow which follows the previous data flow in the control flow.

So ... how do I access the new identity key and have that assigned to a column?

I can use an OLE DBE Source to SELECT IDENT_CURRENT('table_name') as 'TableName' for instance. But then how do I assign that to a variable or add that value to a column in each row of the table I want to save to?

I have tried creating a column with the value 1 in the two data sources and then using a Sort on them both and doing a Merge Join to get acess to the IDENTITY KEY but that a bit of a mess really ... very awkward.

There must be a far better way ...

Your help is very much appreciated.

MarkThis might not be the best solution but this is what I am doing:

In your first data flow, don't use Ole DB Destination for inserting rows.
Instead, use OLE DB Command Transformation.

Write a stored procedure with parameters that will do these steps:

Insert into table 1 values (<values from parameter_list>)
//table 1 is having the identity column

Insert into table 2(col1, col2, identity_col)
values ('foo', 'bar', SELECT @.@. IDENTITY)
Call this stored procedure from Ole DB Command Transformation with appropriate parameters.

HTH

thanks,
Nitesh|||

A few options come to mind:
1. call a stored procedure that would insert the value and return the identity key used back to you. This would be done through an OLEDB Command destination but as a result will be slow since it will be called once for each row.
2. generate the keys in the pipeline itself and write to the identity column. You can initialize the seed before the data flow starts in a variable and get the increment into another variable.
3. Nitesh provided another way of having the stored procedure insert into two tables. This will be slow as well.

regards,

ash

|||Thank-you very much Nitesh and Ash - that has given me some options. :)|||The other option is to insert all the data into a temp table. and then in another task have an OLEDB source that performs the insert into the proper table but using the new select syntax to return the rows with the identies that were generated.

This does mean splitting your flow into multiple data flow tasks but should work. Even with the overhead of inserting the data twice it is likely to be more performant than using individual sql calls.

You take this to the another level and use events to have the processing of the data from the temporary table into the final table being handled on a buffer by buffer basis.

I am working on soemthing that would wrap this up in a single component