MySql SSIS Hangs with ODBC Drivers

Hi, we are writting a SQL Server Integration Services package to import data from a MySql database to a Sql Database.

We are using ODBC 3.51 Drivers to connect to the MySql Database in SSIS. The package runs perfectly in design mode. When we schedule the package to run, the package seems to hang about 1/3 of the times.

What can this be. We used to the package and ran it from a Sql Server 2005 to a Sql Server 2005 database. This setup works perfectly. When i'm doing MySql to Sql Server 2005, 1/3 of the times, it does not work.

Can anyone help me with this?

Giovan Gentile
Ayuda Media Systems

Implementation of ODBC is different by different vendors and for each different data source (after all, that was the goal of it). Therefore because an ODBC connection works fine with a particular source database is no guarantee an ODBC connection against a different database will work for your purposes- _the implementation code is totally different_.

Also, allowing implicit data type conversions (in my experience) is often the source of problems in moving data between different RDBMS implementations. Create views in the source to explicitly make any data type conversions required for the Sql Server destination. That can only help...

Ken

|||

Hello Ken,

Importing from mySQL with the 3.51 works fine for me.

Does your package "hangs" on big resulsets specifically ? If so, it could be that your datasource in configured to cache results from mySQL before making them available to the consumer. That's the default behavior.

Turning on "Allow big results" and "Don't cache result (forward cursor only)" options at the ODBC datasource level fixed the pb for me.

Hope it helps.

--

Jerome

MySql 64 bit driver and SSIS

If anyone have been able to install the MySql ODBC driver on XP 64 bit and get it to work with SSIS, PLEASE tell me how you accomplished it.

Duplicate post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2055272&SiteID=1)

Some related posts that might help :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=165801&SiteID=1

MySql 64 bit driver and SSIS

If anyone have been able to install the MySql ODBC driver on XP 64 bit and get it to work with SSIS, PLEASE tell me how you accomplished it.

Duplicate post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2055272&SiteID=1)

Some related posts that might help :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=165801&SiteID=1

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS Presentation.

I was demonstrating some of SSIS's capabilities to our Software Development Manager and VP of IS. (We are deciding which ETL tool to use for our DW project. So goes this project goes the house).They liked what they saw and were crazy over Fuzzy lookup but commented that SSIS seems very much oriented toward the programmer side of the house. It was, maybe, not the best tool for the Business analysts who are working closely with us on this project.

Any comments on their comment?

I wonder if SSIS "data source views" (which I haven't looked at at all) address their concerns?

Barkingdog

SSIS is a programmer/developer tool. All ETL tools are programmer/developer tools.

Exactly what do they want for the business analysts? SSIS is a platform for providing the data that business analysts may use - it is NOT something to be used by business analysts themselves.

-Jamie

|||

I know the Analysts are looking for a design tool they can use to create a Logical model of the warehouse. Later on the developers will convert the logical into a physical model. Since the analysts have a great understanding of the business implications of our data they will also be the ones completing the "Description" field of each field in the Data dictionary. I guess the company is looking for one product that can do all of this. (logical modeling, data dicitonary, let analysts get involved in the process, satisfy developer needs. They seem to think that the Sunopsis product I mentioned has better coverage of all these areas.)

Barkingdog

|||

I see. I've never seen Sunopsis but would be surprised if it has a logical data modelling tool. But hey, that's good to know.

Personally I use ERWin for this sort of stuff. I like the seperation of data modelling from implementation. To me, design and implementation are different disciplines and should be treated as such. That's just my opinion of course.

In response to your earlier question DSVs are not, in my opinion, a tool for data modelling. DSVs are something you build AFTER the physical model has been implemented. And before you have the physical model you have to build the logical model.

-Jamie

|||

It's worth noting that SQL Server does have exactly the capabilities you are seeking, barking - perhaps not quite in the way you have currently thought.

Analysis Services is the market leading tool for OLAP - which is, of course, a high performance architecture for delivering business analytics to end users.

In Analysis Services it is possible to build a cube - an analytic model - in a logical mode, then generate the physical model from that. These are called top-down cubes.

See: http://msdn2.microsoft.com/en-us/library/ms174482.aspx and http://msdn2.microsoft.com/en-us/library/ms174883.aspx

hth

Donald