6 Replies Latest reply: Jan 29, 2016 8:20 AM by Mo Abdolrahim RSS

    Error using ODBC or OleDB in Script->PostProcess




      I had previously written a VB.NET application that ran as a PostProcess in DataWatch Automator.  The application would simply read in a CSV file that was generated by a DataPump Process, do some formatting and data massaging so it can then be imported in to our financial system (Great Plains).


      Since we moved to DataWatch Automator (Version 12.4) my script will not run anymore.  Since, DataWatch Automater has practically full access to the .NET platform for programming, we thought it would be easier to move the code from external application in to a PostProcess inside of Automator.  The problem i'm having now is that anytime i reference ODBC, OleDB, or the SQL driver (just as a test), i get the following error:


      "Compile Error: ErrorCodeDomCompilerError Number:BC30002, Line:63, ErrorText:Type 'OdbcConnection' is not defined."


      Here is my code so far (i haven't copied it all over to Automator yet, just doing sections of code at a time):


      ' Default Imports...

      Imports Datawatch.Common.Scripting.Api.UserScripting

      Imports Microsoft.VisualBasic

      Imports System

      Imports System.Collections.Specialized

      Imports System.Diagnostics

      Imports System.Windows.Forms


      ' Imports from the process...

      Imports System.IO

      Imports System.Data.Odbc

      Imports System.Math

      Imports System.net.Mail

      Imports System.Xml

      Imports System.Collections

      Imports System.Data.Odbc.OdbcCommand

      Imports System.Data

      Imports System.Data.OleDb



      Module Script

        ' Global Declarations from the process...

            'Dim Variables

          Dim d As Date = Now

          Dim mon As Integer = Month(d)

          Dim yr As Integer = Year(d)

          Dim theDay As Integer = Now.Day


          'Configuration settings

          Dim ImportPath As String = "\\fts-datapump-vm\MONARCH\APPS\SCOTIA_VISA_FI\CSVIN\"

          Dim ImportFile As String = "VISAEXPORT_FI.csv"

          Dim ImportFilePath As String = ImportPath & ImportFile


          'Export Settings

          Dim ExportPath As String = "\\fts-gpmr-vm\GP_IMPORTS\VISA\"

          Dim ExportFileName As String = "VISA_IMPORT_FI_"

          Dim ExportFilePath As String = ExportPath & ExportFileName

          Dim HSTGLCode As String = "2284-00-0"

          Dim TotalEntryGLCode As String = "5997-00-0"


          'Get Email Settings.  Separate emails with comma

          Dim EmailTo As String ="someone@email.com"

          Dim EmailCC As String = "someoneelse@email.com"

          Dim EmailFrom As String = "datapump@email.com"

          Dim MailServerAddress As String = "XX.XX.XX.28"

          Dim EmailSubject As String = "Visa Upload Complete"


          Dim a_FusDiv As New ArrayList

          Dim a_SplitDiv As New ArrayList



          'Declaring various variables that may be declared multiple times throughout the code. 

          Dim TranType, DivCode, PostMonth, GLCode, Ref, Desc, Qry, FileName, Vendor As String

          Dim TranNum As Integer

          Dim PostDate As Date



        Function PostProcess(Log as JobLog) as Boolean

          Log.AddEvent("In PostProcess")

          PostProcess = True


          ' PostProcess steps from the process...

           Dim SumHst, SumAmt, SumNetHST, NetHST, FITotal, HST, iHST As Decimal

           Dim Qry2 As String

           Dim conn As OdbcConnection

           'Dim conn2 As OdbcConnection

           'Dim res As OdbcDataReader

           'Dim res2 As OdbcDataReader

           'Dim cmd As OdbcCommand

           'Dim cmd2 As System.Data.Odbc.OdbcCommand


           'Dim conn As OleDb.OleDbConnection


           Dim s As StreamWriter

           Dim fs As FileStream



      '=============================END OF CODE FOR NOW=====================




      The line that says Dim conn as OdbcConnection always fails.  I've tried OleDB as well and get the same error.  I have the proper Imports called that i would normally use in Visual Studio to make an ODBC connection.  Do anyone have any suggestions?  Is there something i'm missing?  I can't imagine having .NET functionality and not being able to use a database/text driver to connect to an external data source.  Any help would be greatly appreciated!




        • Re: Error using ODBC or OleDB in Script->PostProcess
          Olly Bond

          Hello Dave,


          Sorry this didn't get picked up - I'm no programmer so was hoping someone more expert would jump in. Automator lets you massage the data pretty well yourself - you might find that a summary export using some calculated fields will give you the massaged CSV you need, and that the distribution options in Automator will let you copy it via ftp, email it, etc.


          Best wishes,



            • Re: Error using ODBC or OleDB in Script->PostProcess
              Mo Abdolrahim


              If you are using 64bit Automator then you need to add the following in the references and imports tab (in the Assembly box):




              Change the path if you are using 32bit Automator:  C:\Windows\Microsoft.NET\Framework\v4.0.30319\system.data.dll

              For using the odbcconnection object you need to add system.odbc.data to the namespace.



                • Re: Error using ODBC or OleDB in Script->PostProcess

                  I got it!  Thanks so much!


                  I added the reference: “C:\Windows\Microsoft.NET\Framework\v4.0.30319\system.data.dll” and now the ODBC connections work for me now.  I really appreciate your help with this!


                  I do have another question tho if you could possibly help out.  Is there a way to change who an email is sent from in the Distribution Export within Automator?  In previous versions of datapump, we could always go in to the Distribution Export for a project and set who the email is sent from as well as who it should be sent to, CC and BCC.  In Automator when I edit the Distribution Export, the “Send From” field is grayed out or disabled and can’t be changed.  Since this is a project that we originally created in an older version of datapump and imported it in to Automator, I’m guessing it kept the same person who was originally set up as the email sender.  We want to change who it’s sending as because that person no longer works with us.


                  I noticed I can create a new distribution export but the default “Send From” field is the datapump administrator (FDatapump@fortisinc.com<mailto:FDatapump@fortisinc.com>) and also cannot be changed in the distribution export as it’s grayed out/disabled.  Again, in previous versions we could easily change any of the “send to” and “send from” addresses in a distribution, now we can’t.


                  I know under System Settings in Automator I can change the administrator email to be something different but that won’t help us here.  I have a project that ideally would send a summary report out via email as one of our employees (btilley@fortisinc.com<mailto:btilley@fortisinc.com>) rather than what it says now (annlush@fortisproperties.com<mailto:annlush@fortisproperties.com> who no longer works for us).


                  Hopefully this makes sense and someone can help me or shed some light on whether this can be changed or not.  I’ve attached a screenshot to help illustrate what I’m looking for.







                  Dave Brinston | Systems Analyst