How do you define a MySQL variable in SSIS

Question

I am loading data from a MySQL data table. The table has a large text column, and when the number of records is over 15,000 the SSIS connection times out. So I thought I would try bringing the data over in blocks. I created a query to get the number of records from the source table and create an object containing starting and ending record numbers until all the records are accounted for. Then I set up a For-Each control to read the data in. I used an expression to define the query and it creates a query that contains the starting and ending record numbers I need. I can evaluate the expression and paste it into MySQL and it works just as intended.

Everything would be great except for the fact that I have to use MySQL variables in the query. And even though the variable name is between the quotes, when SSIS actually tries to execute the query it tells me that the parameter @curRow must be defined, but that variable is for use in MySQL. Anyone have any ideas how to get around this? My first thought was to create a string variable in the package named curRow

The expression I am using is:

"SELECT id,
        report_html,
        CASE
          WHEN `TimeStamp` < '1900-01-01 00:00:00'
            OR `TimeStamp` IS NULL THEN
                 CAST('1900-01-01' AS DATETIME)
          ELSE `TimeStamp`
        END AS `TIMESTAMP`,
        @curRow := @curRow + 1 AS row_number
 FROM   corp_inv, (SELECT @curRow := 0) AS r
 HAVING row_number BETWEEN " + (DT_STR, 6, 1252) @[User::LoVal]  + "
                       AND " + (DT_STR, 6, 1252) @[User::HiVal]  + ";"

And the query created by the expression (which does pull up the 3,999 records in MySQL) is:

SELECT id,
       report_html,
       CASE
         WHEN `TimeStamp` < '1900-01-01 00:00:00'
           OR `TimeStamp` IS NULL THEN
                CAST('1900-01-01' AS DATETIME)
         ELSE `TimeStamp`
       END AS `TIMESTAMP`,
       @curRow := @curRow + 1 AS row_number
FROM   corp_inv, (SELECT @curRow := 0) AS r
HAVING row_number BETWEEN 1
                      AND 3999;

I tried but that doesn't work. A backslash won't pass evaluation, a forward slash will but won't execute and putting a backslash directly into the source SQL command text editor and trying to preview the data gives an error. — Jim, Jan 17 '13 at 16:13
Thanks for the help, no luck. I've tried \\@curRow and \@\curRow, `@curRow` as well as using forward slashes. It seems they would have put a means of passing a string that doesn't need validation. I'm grasping at straws also. — Jim, Jan 17 '13 at 16:20
I've tried playing with the connection settings, most don't save when changed. Nothing works, I guess I'll have to abandon this attempt. I'll see if the hardware gurus can extend the timeout period (changes connection settings do not save). We are extracting data from a 3rd party which uses GUID coloumns for primary keys. They will not create views I can work with or add an integer column I can use to query the data in blocks. And they tend to process records in batches, so using a date for criteria does not work as they may create 30,000 records at once. — Jim, Jan 17 '13 at 16:56

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

This SO post suggests adding

;Allow User Variables=True

to the connection string.

(Also check out MySQL's documentation on .Net connection strings for all possible properties.)

One other suggestion would be to use a Script Component Source to go pure .NET and avoid whatever whinginess is impeding using MySQL variables.

edited May 23 '17 at 12:03

Community

1
1

answered Jan 17 '13 at 21:18

Kyle Hale

7,912
1
37
58

How do you define a MySQL variable in SSIS

1 Answers1