May 29, 2008 at 12:14 pm
I am working in Microsoft Visual Studio 2005 (SSIS) and using a Script Component to obtain a billing period ID from a database and setting the global variable BillingPeriod to this ID. I am then trying to use an OLE DB Command to get a result set using the following SqlCommand:
SELECT ModemNumber, Charge FROM dbo.FixedCharges WHERE BillingPeriodID = @[User::BillingPeriod];
The error states:
Error at Billing Database [OLE DB Command [1645]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14... "Statement(s) could not be prepared."... "Must declare the scalar variable "@".".
I assume that I am referencing the global variable incorrectly.
Thanks in advance for assistance!
Dave.
May 29, 2008 at 1:00 pm
Make the command:
SELECT ModemNumber, Charge FROM dbo.FixedCharges WHERE BillingPeriodID = ?
and then click the parameter mapping button to map your variable to the parameter.
May 29, 2008 at 3:42 pm
Thanks for the info Michael,
I could not find the parameter mapping button (I am using a script task in the data flow tab), but I tried something based on your recommedation and it seemed to get rid of that error.
There is still an issue though...
in case you need the information, I am in the data flow tab and have the following set up:
OLE DB Source -> Script Component -> OLE DB Command
When I run it, the OLE DB Command fails first, then the Script component... to which I respond, 'huh?' Anyway,
The script component contains the following code:
Imports System
Imports System.Data
Imports System.Math
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
' First make DateTime objects equivalent to the UNIX Epoch.
Dim startDate As New DateTime(1970, 1, 1, 0, 0, 0)
Dim endDate As New DateTime(1970, 1, 1, 0, 0, 0)
Dim startPeriod As Double
Dim endPeriod As Double
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
' grab the dates in question
startPeriod = Row.StartPeriod
endPeriod = Row.EndPeriod
' Add the number of seconds in UNIX timestamp to be converted.
startDate = startDate.AddSeconds(startPeriod)
endDate = endDate.AddSeconds(endPeriod)
End Sub
Public Overrides Sub PostExecute()
Dim vars As IDTSVariables90 = Nothing
' Set the variables
Me.VariableDispenser.LockOneForWrite("User:startDate", vars)
vars(0).Value = startDate
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("User:endDate", vars)
vars(0).Value = endDate
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("User:startPeriod", vars)
vars(0).Value = startPeriod
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("User:endPeriod", vars)
vars(0).Value = endPeriod
vars.Unlock()
End Sub
End Class
And the OLE DB Command's SqlCommand is:
SELECT ModemNumber, Charge FROM dbo.FixedCharges WHERE BillingPeriodID = ? @[User::BillingPeriod]
I get the following error:
The variable cannot be found... name may have changed or may not have been created...
followed by
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser90.LockOneForWrite(String Variable, IDTSVariables90& ppVariables)
at ScriptComponent_4c30a5c9ced242adb3141f8ddfa885f0.ScriptMain.PostExecute()
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()
I have only just started playing with SSIS :crazy: but it looks like it is going to be interesting!
Also, I have not searched for recommended books yet - but if you know any you would like to mention...
thanks again!
May 30, 2008 at 5:48 am
Ok, the variables in the script component:
Me.VariableDispenser.LockOneForWrite("User:startDate", vars)
should be:
Me.VariableDispenser.LockOneForWrite("startDate", vars)
Make sure you specify the variables in the ReadWriteVariables custom property of the script component.
Rather than summing information in a script task, you could use a conditional split in your data flow and an Aggregate component to sum your data and put it into a variable. This may be easier to maintain in the long run.
The OLE DB Command component is a data flow transformation - meaning it has inputs and outputs for a data flow. So, you would normally wire it to fields in your data flow, not to a variable. Do you understand that this command will run once per row in your data flow? It is also not designed to return a data set, so running a select will not do much. It is for inserts and updates based on the records in your data flow.
Perhaps you could post what you are trying to accomplish and I can help get you back on track.
May 30, 2008 at 9:52 am
Hi Michael,
I had a feeling I was trying to use a metric tool on an imperial fastener....
Here's what I am trying to accomplish... I have two databases:
1) stores a monthly total that each unit was charged (we get this from our supplier)
2) stores each charge that the unit incurs (we produce this and bill our customers based on this)
I am trying to set up a program which will alert us to over or under charging our customers so we can correct this before sending them a bill (this was done manually up until I get this done).
The data I get from our supplier lists a start and end time based on seconds from the UNIX Epoch, our database uses datetime for each entry. The script I have made receives a result set with only one row and converts the start and end times into date objects. With this information I need to do the following:
1) grab all the results from out database based on unit, and in between start and end time
2) determine if the charges are within +/- $x, if not then flag entry
3) determine if any charges are in our data but missing from our suppliers data
4) determine if any charges are in our suppliers data but missing from ours
Thanks again for the assistance - I am now looking into aggregates!
Dave
May 30, 2008 at 12:17 pm
Where I am now...
Control flow:
- Sequence container
--- data flow
------- OLE DB source (get single row result set which contains UNIX Epoch start and end)
------- script component (change UNIX Epoch into datetime -> enter in global variables)
--- data flow
1a----- OLE DB Source (get data from supplier)
1a----- Aggregate (group unit, sum cost)
1a----- Sort
1a*---- Put in temp table
1b*---- OLE DB Source (get data from our servers using stored procedure and start/end variables)
1b*---- Aggregate (group unit, sum cost)
1b*---- Sort
1b*---- Put in temp table
2*----- SQL compare to look for anomalies
2*----- Report or store anomalies
Note: 1a, 1b run concurrently (I think) then the results are used in 2. (*) is planned...
Just wondering if I am using the correct tools - heading for lunch now... hope your day is going well!
Dave
May 30, 2008 at 12:30 pm
That looks reasonable. You don't really need to use temporary tables (or tables - temp tables may turn into a pain for you). You could pull both data sets in one data flow and compare the results right in SSIS, but storing the data will probably be useful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply