May 18, 2015 at 11:11 am
I'm trying to issue multiple commands to a mySQL database using an SSIS 'Execute SQL Task'. A single Execute SQL task with either one of the commands below works fine.
TRUNCATE TABLE profiler_current.email_profiler;
INSERT INTO
profiler_current.email_profiler( email_address)
SELECT
gc.`E-mail 2 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
gc.`E-mail 3 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
gc.`E-mail 4 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
FROM
src_sfdc_current.sfdc_contacts sc
UNION
SELECT
Private_Email__c
FROM
src_sfdc_current.sfdc_contacts sc;
However as soon as I combine both commands into a sing task (either as a direct input or a file connection) I get the following error:-
'ERROR [42000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.17]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO
profiler_current.email_profiler( email_address)
SELECT'
I know there is no syntax error with my SQL as the whole script runs in mySQL and each command runs ok via the Execute SQL Task. Does anyone know what the problem is here, am I missing something? I can't seem to find anyone who has had this problem before (or indeed examples of any successful uses with mySQL). I know I can use an execute process task instead and run the mySql script from a batch file but this compromises security as I have to issue the password in clear text or interactively.
I'm using a connection manager type = ADO.NET:System.Data.Odbc.OdbcConnection This connection works fine as a connector for an ADO Net Destination.
May 18, 2015 at 11:43 am
Why not cut your losses? Create two ExecuteSQL tasks and run one after the other.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 18, 2015 at 11:49 am
Yes that would be fine if I had only a handful of statements but the full script I want to run will have many statements so such an approach would be unwieldy in my situation. I just used two statements as an example as it always fails as soon as I have more than one.
May 18, 2015 at 12:30 pm
I am no mySQL developer, so please excuse me if this is really obvious, but I did a quick search and found this. Maybe the section marked 'The semi-colon outside stored routines' would be worth a try?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 18, 2015 at 1:18 pm
Thanks Phil - it's not quite a solution but it's a lead. So the following works.
TRUNCATE TABLE profiler_current.email_profiler
go
INSERT INTO
profiler_current.email_profiler( email_address)
SELECT
gc.`E-mail 2 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
gc.`E-mail 3 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
gc.`E-mail 4 - Value`
FROM
src_google_current.google_contacts gc
UNION
SELECT
FROM
src_sfdc_current.sfdc_contacts sc
UNION
SELECT
Private_Email__c
FROM
src_sfdc_current.sfdc_contacts sc
go
(though I'm not entirely sure why). The problem this then gives is that the script no longer works if I run it native in mysql. issuing the command 'delimiter go' at the start of the script helps, but, this falls over if the script has the string 'go' (which occurs twice for example in '......FROM
src_google_current.google_contacts gc......).
So there is a bit more to it but maybe your input and these comments will trigger further comment.
Incidentally after opening the connection but before issuing the main sql I am issuing
set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply