Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Postby LBUser » Thu 02 Aug 2012 06:18

Apologies if this is not the correct forum for posting this particular query, but I'm still not sure if it's a Visual studio issue or a quirk with the DotConenct driver when run under VS2005.

I am using VB script within an SSIS script object to dynamically construct a select query on a MySQL database - based on input variables to construct the date range - using the DotConnect for MySQL .Net provider.

I'm editing the script to add in a column in the extract query - nothing extraordinary.

I have a copy of the same SSIS package in both a VS2005 and vs2008 solution files.

When I open the script in 2005 and try to add the column I get three specific errors pertaining to Types not defined for MySqlConnection, MySqlCommand and MySqlParameter.

The relevant header code is as follows (I've omitted the dynamic SQL string):
Option Strict Off
Imports System
Imports Devart.Data
Imports Devart.Data.MySql
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text.RegularExpressions

Public Class ScriptMain
Inherits UserComponent

Dim MySqlConn As MySqlConnection
Dim MySqlCmd As MySqlCommand
Dim MySqlParam As MySqlParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

MySqlConn = Me.Connections.Connection.AcquireConnection(Nothing)
MySqlConn.Open()

End Sub
(I've omitted the dynamic SQL string and return code that would normally be here)

When I open the code in VS2008 I get no such validation errors and the script runs without issue:

For reference that code is:
Option Strict Off
Imports System
Imports Devart.Data
Imports Devart.Data.MySql
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Text.RegularExpressions

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent

Dim MySqlConn As MySqlConnection
Dim MySqlCmd As MySqlCommand
Dim MySqlParam As MySqlParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

MySqlConn = Me.Connections.Connection.AcquireConnection(Nothing)
MySqlConn.Open()

End Sub

So I'm not sure whay the same script fails validation in VS2005 but runs in VS2008, and am not sure if it's a VS bug or a known quirk with the dotConnect for MySQL VS2005 driver? Any assistance or thoughts greatly appreciated.
LBUser
 
Posts: 4
Joined: Thu 02 Aug 2012 05:52

Re: Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Postby LBUser » Thu 02 Aug 2012 07:53

I also posted this to a .Net forum and a respondent there came back with the likely explanation that the latest assembly would be compatible with .Net 3.5+ whereas VS2005 is only compatible with .Net 2.0 - hence why VS2008 could compile and run the code and VS2005 could not.
LBUser
 
Posts: 4
Joined: Thu 02 Aug 2012 05:52

Re: Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Postby Shalex » Mon 13 Aug 2012 12:27

LBUser wrote:When I open the script in 2005 and try to add the column I get three specific errors pertaining to Types not defined for MySqlConnection, MySqlCommand and MySqlParameter.

Please make sure that:
1. The .NET Framework version of your project is set to 2.0. The way to check it: http://stackoverflow.com/questions/4692944/how-do-i-find-the-net-framework-version-used-in-an-ssis-2008-r2-package.
2. The references to Devart.Data.dll and Devart.Data.MySql.dll are available in your project and valid.
Shalex
Devart Team
 
Posts: 7610
Joined: Thu 14 Aug 2008 12:44

Re: Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Postby LBUser » Mon 13 Aug 2012 20:43

Thanks Shalex, your tidbit of information set me on the right path to resolving this in VS2005. For anyone that follows with a similar issue, make sure that you double check in the VS for Applications Script editor that the devart assemblies are visible and available.

I resolved this issue by copying the following files FROM the C:\Program Files (x86)\Common Files\Devart\dotConnect\5.0\Net2\Common directory:
1)Devart.Data.dll
2)Devart.Data
3)Devart.Data.MySql.dll
4)Devart.Data.MySql

TO THE C:\Windows\Microsoft.NET\Framework\<version> directory (my version was v2.0.50727)

Close and re-open VS2005 and navigate to the VSA editor and you should see 1) that the validation warnings are now gone 2) when you go to Tools.Add Reference that you see the Devart assemblies listed (I previously could not).

Below is an additional reference about adding assemblies.

http://beyondrelational.com/modules/2/blogs/81/posts/11263/referencing-an-assembly-inside-ssis-script-task.aspx
LBUser
 
Posts: 4
Joined: Thu 02 Aug 2012 05:52

Re: Discrepancies between Visual Studio 2005 and 2008 for SSIS Script object

Postby Shalex » Wed 15 Aug 2012 10:03

Thanks LBUser for your feedback.
Shalex
Devart Team
 
Posts: 7610
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL