November 9, 2012 at 9:58 am
this is driving me nuts, it should be soooooo simple :w00t:
I have BIDS 2005 installed on my laptop. I am writing a really simple SSIS package to extract data from SQL2005 on a network server via OLE DB and save it to my laptop as a pipe delimited file. This all works fine when the values are hard coded.
I have made a new OLE DB connection and tested it (it works) Data Access mode is SQL Command.
The command text is
SELECT cppolref,'COMPLETE' as 'Project' FROM opsBatchRecord where BatchID = 1013
This works.
I would like to parameterise the BatchID. I have created a variable [User::BatchID] scope = data flow task, type = single, value = 1013
I have rewritten the query to
SELECT cppolref,'COMPLETE' as 'Project' FROM opsBatchRecord where BatchID = ?
When I click on the Parameters box it is prompting for a value for [Parameter0]. I have set this to be the variable [User::BatchID]
When I hit the [Preview] button I get the message "No value given for one or more required parameters. (Microsoft SQL Native Client)"
I must be missing something obvious, Gan you guys point me in the right direction
I have tried changing the varaible type for Int16,int32, string
I have tried renaming the [Parameter0]
I have tried renaming the ? to ?x (and the parameter name)
No Dice:hehe:
November 9, 2012 at 11:52 am
Have you set a default value for the variable?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 10, 2012 at 2:57 am
Yes i had. Bizarrly it started working about 20 minutes after posting and i swearvi didnt change anything. I hate it when that happens!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply