Post reply

Parameterized OLE DB Source

  • August 12, 2008 at 4:54 am

    #125510

    Hi,

    I'm new to SSIS and I want to know if OLE DB Source component can be parameterized i.e., We define a variable in the scope of the package - TableName (of type string).

    Now, can that be used in OLE DB Source? (like - "Select * from @[User::TableName]")

    I've tried this and it's not working. Is the above statement incorrect syntactically? Or is it that we cannot use user defined variables in OLE DB Source? Is there any other way (like using source script component)?

    Please clarify.

    Manohar

  • August 12, 2008 at 6:44 am

    #856629

    There is an option to setup a SQL command from a variable. However, if you are going to parameterize the Table Name, will the structure also be changing on your dataset?

  • August 12, 2008 at 7:24 am

    #856671

    Hi,

    I've tried using the 'SQL Command using variable', but, there seems to be a limitation.

    This is my requirement -

    I've two tables A, B with the same table structure. I need to use the same package to load two tables using the parameters.

    I've created two variables - Tablename and Command.

    Tablename can take the value A or B

    Command has the value - "Select * from @[User::Tablename]"

    I'm getting an error when I tried in the above mentioned method. Can a variable be used in computing another variable's value? (as above)

    And to answer your question - No, the structure of the dataset will not be changing.

  • August 12, 2008 at 7:28 am

    #856676

    There is no limitation other than 4000 characters.

    So long as the value of your variable is a valid SQL statement, it will work.

    As for the variable using another variable, look at the properties for the variable, set Eval as expression true, click on the ... and build your own statement. You can use other variables in there.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 12, 2008 at 7:30 am

    #856680

    Just reread, you cannot do what you are trying. The value of the variable HAS TO BE A VALID SQL STATEMENT.

    You variable expression will be "Select * From " + @[User::MyTable]

    That will evaluate to Select * From MyTable and it will work.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • August 12, 2008 at 10:46 pm

    #857173

    Thanks a lot for your help.

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

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