December 12, 2011 at 7:55 am
I've done a few script components but this time need to pull 1-n records from a source table for each row in the inputrows, using parameters from existing data flow columns.
We're using OLEDB and the connection/database is the same as the package already uses, so no new connection mgr/connection.
I've done very little .Net and having trouble making a connection to a source table from within the script component. I'm sure it's simple, if someone could just point me to a reference?
thanks in advance!
December 12, 2011 at 8:15 am
I don't understand why you're using a Script task. I would use an Execute SQL task, with an expression built using the values from your columns. If this doesn't make sense, please provide a fuller description of your problem.
John
December 12, 2011 at 9:23 am
Can't use a script task, this is in the data flow. I cannot just update or merge join.
Specifically, I have payments that must be attributed to one of several possible records and must evaluate those records one by one to assess which receives the payment. I cannot allow a payment to be applied to more than one record and must evaluate action type, date, what other actions, what other dates, transactions.
In short, I have to look at a clump of candidate records to determine which receives an entry to the payment column, and which payment column. The logic is not one-size-fits-all update-query ilk.
I can write the payments to a temp table, and then need to open that table from within script component where I have the fact records in the data stream. Some of those will be output with payments, some will be output with no payment.
I had originally thought I would go the other way - scroll thru the payments and maybe attach to fact table staging since not every fact record will have a payment record.
So the problem is really, 2 streams of data, but not same record count nor same fields (minor overlap allowing certainly of client account). How can I get to one data store from another with the ability to evaluate each record? I see no other way than script component for the assessment I need (stored procedure is not an option for this process).
Will eagerly accept ideas!
December 12, 2011 at 2:33 pm
You can use a script task (in the data-flow it would be considered a script-transform) to take in fields and execute OleDB queries against another connection. It really is like straight OLEDB programming. connections, recordsets and readers. For a starter do a google query like "query data using OLEDB vb.net" or replace vb.net with c#.. You will find some sample code that shows the query. You are going to use the data flowing through the script transform to run those queries and output new data.
I hope this helps.
CEWII
December 12, 2011 at 2:51 pm
herladygeekedness (12/12/2011)
Can't use a script task, this is in the data flow. I cannot just update or merge join.Specifically, I have payments that must be attributed to one of several possible records and must evaluate those records one by one to assess which receives the payment. I cannot allow a payment to be applied to more than one record and must evaluate action type, date, what other actions, what other dates, transactions.
In short, I have to look at a clump of candidate records to determine which receives an entry to the payment column, and which payment column. The logic is not one-size-fits-all update-query ilk.
I can write the payments to a temp table, and then need to open that table from within script component where I have the fact records in the data stream. Some of those will be output with payments, some will be output with no payment.
I had originally thought I would go the other way - scroll thru the payments and maybe attach to fact table staging since not every fact record will have a payment record.
So the problem is really, 2 streams of data, but not same record count nor same fields (minor overlap allowing certainly of client account). How can I get to one data store from another with the ability to evaluate each record? I see no other way than script component for the assessment I need (stored procedure is not an option for this process).
Will eagerly accept ideas!
Without knowing the exact details of the evaluation, it's not easy to suggest a better methodology than what you have proposed.
But there may be a way of doing this without executing a query per row of data, which I think is what you are suggesting. If there is a lot of data, this is going to crawl. Think about how you might do it in pure T-SQL - is there a better way?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 12, 2011 at 3:15 pm
I don't think regular ADO connection stuff works here since that doesn't take into account the confines of DTS. I've done some script components before so do understand the data stream aspect of using the transform. I just need to be able to reach outside data! I haven't even made it to the challenge of storing scrollable returned records in a cursor or recordset.
Friday I spent googling for any tidbits specific to this script component, after trying BOL, MSDN. I know people must be doing this, but the needle in the haystack continues to elude me.
What I have so far been able to beg, borrow, steal and otherwise cobble together is this:
Dim myConnMgr As IDTSConnectionManager90 = Me.Connections.Connection
Dim myLiveConn As IDTSConnections90 = CType(myConnMgr.AcquireConnection("DW"), IDTSConnections90)
Dim sqlCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("Select top 100 * from Facttable")
Public Overrides Sub PreExecute()
With sqlCmd
.Connection = CType(myLiveConn, SqlClient.SqlConnection)
.CommandType = CommandType.Text
End With
End Sub
Public Overrides Sub ReleaseConnections()
If myLiveConn IsNot Nothing Then
Connections.Connection.ReleaseConnection(myLiveConn)
End If
End Sub
"DW" is my existing connection manager that I will reuse for accessing other data in that database.
and already have an error on the design surface that I can't read all of:
System.reflection.TargetInvocationException
so can't even run to find out if I'm connecting. I did, however, learn that OLEDB returns unmanaged objects, so the AcquireConnection method is maybe my culprit in the warning message. But this info is specific to script TASK so I can't even be certain of it's validity in script COMPONENT, altho I would think they would function quite similarly. It's clearly stated that you cannot call AcquireConnections for OLEDB.
December 12, 2011 at 3:39 pm
We know it will crawl but since it's part of the ETL for the fact table, we need to attribute the payments in the same package. That's also where I get stuck with SSIS-only.
I cannot get away from record by record evaluation - it's critical to payment allocation to appropriately assess the conditions of the charge, transactions and payment to get the payment with the right record. I think a script component would be much faster than a stored procedure, and I know that sp's are a hard sell here.
I thought about straight update for all and then flipping thru the records in script to "blank out" payments wrongly attributed, but I get heartburn thinking about knowingly writing false data, even if the intention is to go remove it! It would solve the problem as far as just acting on one stream of data without needing the outside store, but I can't imagine selling that process to the boss. Debugging without step thru is a nightmare already, adding that kind of risk to the data is beyond my comfort zone.
I really need the base records for the fact table as the input stream and the payments as an outside store. Not every fact record will have a payment, which makes it all the sadder to have to touch every one of them, but that's what is in the stream.
I could alternatively use payments as input to a script component in a different data flow and lookup relevant fact records to determine which gets the payment. In that scenario, I can add whatever fields I need to the data stream to make a reliable update query to execute post-script. I'd rather act on the fact data before it goes to staging, but can add payments to the staging table instead.
I do have some flexibility over how to make this happen but in the end, Correct Data trumps everything else.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply