Post reply

Data Flow error in OLEDB source

  • 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

  • August 7, 2008 at 2:26 pm

    #854758

    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

  • August 7, 2008 at 2:44 pm

    #854772

    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

  • August 7, 2008 at 3:04 pm

    #854785

    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?

  • August 7, 2008 at 6:22 pm

    #854844

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

    Thanks

  • August 8, 2008 at 6:38 am

    #855087

    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.

  • August 8, 2008 at 6:50 am

    #855101

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

    Thanks,

    Strick

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

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