Data Flow error in OLEDB source

Question

Data Flow error in OLEDB source

stricknyn

SSCertifiable

Points: 6560
More actions
August 7, 2008 at 1:58 pm

#189054

I have a script task which builds this SQL string:
SET FMTONLY OFF EXEC PROVIDER_DIRECTORIES 510,23,'8/7/2008',NULL
This is saved to a SSIS string variable called strSQL. As a test I can take the results of the script task and run the statement in SQL Server Mgmt Studio and it runs just fine. So I know my syntax is correct.
The string strSQL variable is being used in an oledb source in my data flow task. I have SQL command from variable as the data acces s mode and the name of my variable strSQL in the variable name box. When I click ok I get the error "Command text was not set for the command object"
Everything seems correct so I have no idea why it won't take this. Can anyone see if I'm doing something wrong?
Thanks,
Strick

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

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

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

if you right click on the OLE DB and select Advanced Editor, click on the Component Properties tab. Change the ValidateExternalMetaData value to False. This will not validate your string until runtime, which should be what you need.

hth

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Hi, thanks for your response. Unfortunately that didn't work. What's even weirder is that I'm doing something similiar with another package I did a while ago and the one works just fine.

To add: this lets you use stored procedures as sources in packages. I believe it gets the schema and returns it to the meta data prior to actually returning data.

Thanks, Strick

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 3

hmm, for curiosity, what is the value of your string variable in design time? is it empty or does it hold the value of your stored procedure that you want to call so you can get the columns setup in the dataflow?

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

Its empty. My first control flow task, a script task builds strSQL. THen then next task, the data flow uses it.

Thanks

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 5

ok, this is what i did to get your scenario to work. i originally populated the sql string variable with the stored procedure call so i can get the metadata of the columns returned from the sp into my dataflow, at which point i designed the rest of my dataflow as intended.

after that, i setup the data source in the advanced editor to set the validatexternalmetadata to false.

i then blanked out the sql string variable to ensure that a bad call is not sent. i then, as you stated, put in a script task to piece together the string which will be the source of the data flow.

putting those steps together worked in my environment and i was able to execute the package successfully.

stricknyn SSCertifiable Points: 6560 More actions · Answer 6

Hmmm interesting. I'll try that when i get to work.

Thanks,

Strick