Post reply

Setting Variables in an Execute SQL Task and retrieving the results

  • August 13, 2008 at 11:41 am

    #125722

    I'm trying to load a variable with the highest value in a column using the max function. I then want to use that value in another Execute SQL Task to select all values from another table that are greater than the variable.

    Frustrated like you wouldn't believe.

    Can someone please help with the syntax - the first command is checking a SQL 2000 table using a Native OLE DB\SQL Native Client type Connection Manager. The second is connecting to a MySQL table using a .NET Providers\MySQL Data Provider type Connection Manager. The connector was downloaded from MySQL and is MySQL Connector Net version 5.1.6.

    Connections are all working, but the can't figure out the syntax. If need be, I can get around accessing the MySQL DB by dumping to text files, but I still need to be able to load the variables for importing.

    Thanks in advance,

    Ed

  • August 14, 2008 at 2:42 am

    #857889

    Could you give an example of the problems you are getting, plus the sql syntax you are using?

    Tom

    Life: it twists and turns like a twisty turny thing

  • August 14, 2008 at 9:42 am

    #858191

    I've made some progress since the posting - First issue - my select statement was to find the max(ID) in a table - took a lot of searching (SQLIS.com) / trial and error to find out that the variable type returned had to be a string type. Got that straight, and then found out that I had to pass the variable in the properties of the Data Flow task - not the datareader source item (abbadinatasha.blogspot.com).

    Anyway - I'm now trying to figure out how to take the output that is a string and convert it to a number to a select greater than from a MySQL table.

    All this seems very painful compared to DTS, but I'm still plugging away.

    Thaks for any help,

    Ed

  • August 14, 2008 at 11:28 am

    #858270

    In Execute SQL Task you will have to use parameters to return values from your table. I guess you are using ODBC driver to connect to MySQL.

    Your query should look something like:

    SELECT Col1,Col2 from TableName Where Col1 = ?

    Follow this (http://technet.microsoft.com/en-us/library/ms140355.aspx) to check what parameter marker and name you should be using for ODBC connection.

    Add parameter to your Execute SQL task with Parameter Name = 1 (for ODBC connection), use appropriate datatype.

    HTH

    ~Mukti

Viewing 4 posts - 1 through 3 (of 3 total)

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