I tried your script and recreated your environment completely and i didn't see any error.
Ran perfectly and created all tables and transferred all data successfully
i tried running under a sql agent job and still no error was generated
but i guess you can update your insert script by making sure the column gets converted under all conditions
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
or
[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE Null END
change your script to
INSERT INTO OPENQUERY (
ZMMS_ORA
'SELECT
"COMPANYID",
"BO_ID",
"AS CM",
"AS RSM",
"AS SE",
"TS CM",
"TS RAE",
"TS RSM",
"TS SE",
"BBONE AM",
"ASM",
"WCT ASM",
"LS TSM",
"WCT TSM",
"TS TSM",
"AS PM",
"AS DEV",
"TS PM",
"TS DEV",
"TS TECH",
"BB EXEC",
"CHM",
"ANGEL Client Manager (CM)",
"ANGEL Engagement Team",
"BbOne Client Manager (CM)",
"Blackboard Executive",
"CE/Vista Hosting Mgr (ASM)",
"CE/Vista Tech Support (TSM)",
"Complex Hosting Manager (CHM)",
"Learn Client Manager (CM)",
"Learn Hosting Mgr (ASM)",
"Learn Reg Sales Mgr (RSM)",
"Learn Sales Engineer (SE)",
"Learn Services Developer",
"Learn Services ICM Mgr",
"Learn Services Project Manager",
"Learn Tech Support (TSM)",
"Transact Client Manager (CM)",
"Transact Project Manager",
"Transact Reg Acct Exec (RAE)",
"Transact Reg Sales Mgr (RSM)",
"Transact Sales Engineer (SE)",
"Transact Services Technician",
"Transact Tech Support (TSM)",
"Xythos Tech Support (TSM)" FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
)
SELECT "COMPANYID"
,[BO_ID] = CASE WHEN isnumeric([BO_ID]) = 1 THEN [BO_ID] ELSE CAST([BO_ID] as DECIMAL(31,0)) END
,"AS CM"
,"AS RSM"
,"AS SE"
,"TS CM"
,"TS RAE"
,"TS RSM"
,"TS SE"
,"BBONE AM"
,"ASM"
,"WCT ASM"
,"LS TSM"
,"WCT TSM"
,"TS TSM"
,"AS PM"
,"AS DEV"
,"TS PM"
,"TS DEV"
,"TS TECH"
,"BB EXEC"
,"CHM"
,"ANGEL Client Manager (CM)"
,"ANGEL Engagement Team"
,"BbOne Client Manager (CM)"
,"Blackboard Executive"
,"CE/Vista Hosting Mgr (ASM)"
,"CE/Vista Tech Support (TSM)"
,"Complex Hosting Manager (CHM)"
,"Learn Client Manager (CM)"
,"Learn Hosting Mgr (ASM)"
,"Learn Reg Sales Mgr (RSM)"
,"Learn Sales Engineer (SE)"
,"Learn Services Developer"
,"Learn Services ICM Mgr"
,"Learn Services Project Manager"
,"Learn Tech Support (TSM)"
,"Transact Client Manager (CM)"
,"Transact Project Manager"
,"Transact Reg Acct Exec (RAE)"
,"Transact Reg Sales Mgr (RSM)"
,"Transact Sales Engineer (SE)"
,"Transact Services Technician"
,"Transact Tech Support (TSM)"
,"Xythos Tech Support (TSM)"
FROM CRMRPT..B_RPT_B_TEAM_ROLE;
There's probably an error occurring somewhere in the process, because that would cause exactly what you're seeing.
Here's why:
When you define a view, the views or tables referenced by that view must already exist:
mysql> CREATE VIEW broken_view AS SELECT some_column FROM nonexistent_table;
ERROR 1146 (42S02): Table 'test.nonexistent_table' doesn't exist
For this reason, mysqldump generates a dump file that includes statements defining all of the views as equivalent(ish) tables, then goes back one by one and drops the tables and creates the views. This avoids the problem that would occur if any view referenced another view, or referenced a table that hadn't been restored yet (tables are restored in alphabetical order).
The error could be while generating the dump file -- if any of your existing views are invalid -- for example, they reference tables or columns that don't exist, or reference DEFINER users that don't exist or don't have proper permissions, or if you don't have sufficient privileges -- their definitions can't be extracted, because SHOW CREATE VIEW only works on valid views.
If, by way of illustration, you create a view against a table, then drop the table, you'll find that you can no longer SHOW CREATE VIEW against that view.
If your views reference tables in a different schema that aren't on the destination server, you'd have a similar problem, because their definitions won't be accepted.
Being someone with a strong distaste for almost all graphical tools, I can't stand Workbench, but I believe it uses mysqldump under the hood, so this is likely to be the nature of the problem.
The export file should be human-readable. If, at the end of the file, there are view definitions, then a problem is occurring when you're restoring the file. If not, then you've got a problem preventing the export from finishing, and Workbench may not be showing you the error... but an incomplete export (or import) would be exactly the explanation for views being defined as tables on the destination server.
Best Answer
There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).
Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.
While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.