Invalid Column Name when using parameter

[aquila125]

Hi all,

For a DTS transformation I would like to use the following query as source:

select
(select top 1 extnrwg_060 FROM werkgever_060 where intnrwg_060 = intnrwg_250) as extwgnr,
factuurnr_250,
factuurperiode_251,
datepart(year,factuurdatum_250) as jaar,
datepart(month,factuurdatum_250) as maand,
datepart(day,factuurdatum_250) as dag,
facturatiecode_251,
bedrag_251
from facturatiehoofd_250
INNER JOIN facturatiedetail_251 ON factuurnr_251 = factuurnr_250
where
(facturatiecode_251 in (91001,91100,91101,92001,92002,92003,92004,92005,92006,92007,92008,92009,92010,92011,92100,93001,94001,95001,96001))
and factuurnr_250 > ?
and factuursoort_250='F'


When I try to add the parameter (or try to parse the query) I get a 'Column Name Invalid' error on the factuurnr_250 column. When I remove the question mark and replace it with a nr, the query parses just fine.
What am I doing wrong?

 

[TomKane]

Hi,

I think you would be better off putting that into an SP with an input parameter.

Regards,
Tom

 

[triggerhappy]

Everything seems fine...just to confirm hope we have updated the "Input Parameter" tab.

Alternative: You can use VBScript to update the Source.

dim varSql

set varSql = "select
(select top 1 extnrwg_060 FROM werkgever_060 where intnrwg_060 = intnrwg_250) as extwgnr,
factuurnr_250,
factuurperiode_251,
datepart(year,factuurdatum_250) as jaar,
datepart(month,factuurdatum_250) as maand,
datepart(day,factuurdatum_250) as dag,
facturatiecode_251,
bedrag_251
from facturatiehoofd_250
INNER JOIN facturatiedetail_251 ON factuurnr_251 = factuurnr_250
where
(facturatiecode_251 in (91001,91100,91101,92001,92002,92003,92004,92005,92006,92007,92008,92009,92010,92011,92100,93001,94001,95001,96001))
and factuurnr_250 > " & DTSGlobalVariables("factuurnr_250").Value & "
and factuursoort_250='F'"

assign the same to the source component of the datapump task
Dim oPkg, oDataPump, sSQLStatement

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

oDataPump.SourceSqlStatement = varSql

right click on the Source datapump and "Workflow Properties" to check for the exact id : DTSTask_DTSDataPumpTask_1

-----

TriggerHappy