How do I reference a global variable in the SqlCommand of an OLE DB Command?

Question

Post reply

How do I reference a global variable in the SqlCommand of an OLE DB Command?

dgates-756279

Old Hand

Points: 334
More actions
May 29, 2008 at 12:14 pm

#380824

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

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.

dgates-756279 Old Hand Points: 334 More actions · Answer 2

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!

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

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.

dgates-756279 Old Hand Points: 334 More actions · Answer 4

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

dgates-756279 Old Hand Points: 334 More actions · Answer 5

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

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.