4 Replies Latest reply: May 15, 2014 10:00 AM by Bill Watson RSS

    Script Help

    Rags _

      I am wondering if anybody knows of any good resources that would help getting started using the Script tab for a XPRJ project file.  There is a lot that I would like to do with the script, but I am new to using VB in this way.  Any suggestions?

       

      Thanks.

        • Script Help
          Gareth Horton

          Hi

           

          First things first, work out what software you have at your disposal.

           

          If you have  Visual Studio.NET[/b] , then you can use this to prototype your script, using all the features this offers, plus the excellent documentation and samples.  Then you can copy your code into Datapump, add the necessary namespace references and test it.

           

          However, this is not necessary.  You can use any text editor, or the XPRJ Script editor itself to write the code, which is what I do most of the time.

           

          However, if you go the "lightweight" route, then downloading the  .NET 1.1 Framework SDK[/b]  is a must.

           

          This includes all the necessary documentation for the .NET Framework, so that you can find out what methods and properties are available to accomplish your desired task.

           

          At the time of writing (Microsoft URLs seem to have a short shelf life) this could be found  [url="http://www.microsoft.com/downloads/details.aspx?FamilyID=9b3a2ca6-3647-4070-9f41-a333c6b9181d&displaylang=en"]here.[/url]

           

          The next place to look is the  Datapump Scripting Help file[/b] .

           

          This contains all the information on the methods and properties available within Data Pump.  Additionally, there are also a few code examples to help you on your way.

           

          Note that once you have the .NET 1.1 SDK or Visual Studio.NET documentation installed, certain items in the Data Pump Scripting help link in to that.

           

          For example, if you go into the Data Pump Scripting Help, select JobLog Class and click on System.Object, this integrates with the .NET Framework help.

           

          I found that learning from other people's code was the best way, in conjunction with a pressing need to solve a problem.

           

          I just wrote some code to help one of our partners, and it is a little more advanced than some of the examples, but I'll post it anyway.

           

          NOTE: Unfortunately, the forum strips the blank lines, so I put in a comment on every blank line to make it more readable.

           

          Analysing the examples in the help file, along with this code, should be a useful exercise to get you started.

           

          [font="courier"]'This code takes an output file, passes it to the Winzip command line utility for zipping

          'then e-mails it using a global distribution. It assumes a simple process with a single

          'project and a single output.

          '

          '

          'Create a new JobLogItem object.  This allows us to add the zipped file into the

          'Job Log, which the distribution component reads to find out its list of tasks

          '

          Dim NewItem as JobLogItem

          '

          'Do Zipping stuff here, this essentially calls a command line, passing the name of the output file.

          'NOTE: This code only deals with the scenario of a single output file.  This could be adapted for multiple

          'output files easily though.

          '

          'The following code looks tricky, but most of it can be pre-built using the Script Editor "Insert Script|Execute an external command line"

          '

          If Shell("""C:Program FilesWinZipWZZIP.EXE"""" "Log.ExpandMacros("""c:excelzipexcelzip.zip""" + " " + """&[output]""",ProjectID),AppWinStyle.Hide,True,30000) <> 0 Then

            ' Handle command timeout here, by adding an event to the log that the zip operation timed out.

            ' We could also have added an alert if we wanted to

          '

          'The following line is the result of using the Script Editor "Insert Script|Add an event to the log"

          log.addevent("Zip Timed Out")

          '

          End If

          '

          'This code just adds the command line that executed to the log, so we have a record of it.

          '

          log.addevent("""C:Program FilesWinZipWZZIP.EXE"""" "Log.ExpandMacros("""c:excelzipexcelzip.zip""" + " " + """&[output]"""))

          '

          'The main code creates a "fake" output item in the job log

          'The distributor reads this to decide what it needs to do

          'including what files it needs to attach

          'However, note that it still thinks that it has the original

          'Excel file to distribute, so you could either remove that

          'output item from the log - a bit tricky

          'Or be crafty with global distributions

          '

          'So, what you need to do is make sure there is no

          'project distribution set, just set a global distribution

          'to where the zipped excel file ends up

          '

          'This creates a new output item in the job log, note that we specify its type as a file output.

          NewItem = new JobLogItem(Log,JobLogItem.ItemType.FileOutput)

          '

          'Give it a title - note this will then be used if you use a macro in the e-mail distribution

          NewItem.Title = "Test Zipped Excel File"

          '

          'Give it a Description

          NewItem.Description = "This is a test Excel file, zipped for a global distribution"

          '

          'Give it a full path

          NewItem.Location = "C:excelzipexcelzip.zip"

          '

          'Add it to the log as a "fake" output item, so that the distributor knows to process it

          log.AddOutputItemForExport(newItem,ProjectID,"Zip")

          '

          'Now set up a global e-mail distribution at "c:excelzip" before running the process, remember

          'you can also use the naming macros in the e-mail .

          '

          ' /font[/quote]Additionally, if there is anything specific you want to do, why not post it and see if we can work it through on the forum.

           

          I don't know how green you are with programming, but FYI, the lines preceded with ' are comments, they are just notes to explain the code, not actual code.

           

           

           

           

          Originally posted by ewinglee:

          I am wondering if anybody knows of any good resources that would help getting started using the Script tab for a XPRJ project file.  There is a lot that I would like to do with the script, but I am new to using VB in this way.  Any suggestions?

           

          Thanks. /b[/quote]

          • Script Help
            Bill Watson

            Gareth

             

            I wondered if you could cast your eye over this problem we are having - a preexport script that was running okay now causes datapump process to fail.

             

            Objective:[/b]   To truncate a table in Oracle before exporting data to the table, I don’t want to

            overwrite the structure of the table and therefore require to empty the table before appending the Exported data.

             

            I’ve added a PreProcess script (shown below) that compiles when the Assembly and Import references are added, once the process is run it returns an Assembly Name Invalid error ?

             

            Assemblies:[/b]

             

            System.Data.OracleClient: C:WindowsMicrosoft.NetFrameworkv2.0.50727System.Data.OracleClient.dll

            System.Data: C:WindowsMicrosoft.NetFrameworkv2.0.50727System.Data.dll

             

            Imports:[/b]

             

            System.Data.OracleClient

            System.Data

             

            PreExport Script:

             

            [font="courier"]Dim Oraclecon As New OracleConnection("Password=xxxxx;User ID=xxxxx;Data Source=xxxx;")

            Dim sql As String = "TRUNCATE TABLE_NAME"

            Dim myCMD As New OracleCommand(sql, Oraclecon)

             

            Oraclecon.Open()

            myCMD.Connection = Oraclecon

            myCMD.CommandType = CommandType.Text

            try

            myCMD.ExecuteNonQuery()

            Catch myex As Exception

                MsgBox(myex.Message)

            End Try

             

            Oraclecon.Close()[/font][/quote]When we test this we get Script Compiled without Errors Dialog. However when we run the process via Datapump we get the following:

             

            [font="courier"]- <joblog process="CB_TB" jobID="1038">

            - <events>

              <event time="2007-11-01T12:05:39">Created.</event>

              <event time="2007-11-01T12:05:41" alert="SystemAlertProcessFailed">Failed: Microsoft.Vsa.VsaException: AssemblyNameInvalid (0x80133005).</event>

              <event time="2007-11-01T12:05:41" source="distributor">Distribution started</event>

              <event time="2007-11-01T12:05:41" source="distributor" value="complete">Distribution completed</event>

              </events>

              <distribution />

              </joblog>[/font][/quote]The same script, assemblies and imports are removed and readded to the PreExport tab but now it doesn’t compile and shows an Assembly Name Invalid. The dialog shows: Error Compiling Script: Microsoft.Vsa.VsaException: AssemblyNameInvalid(0x80133005)

             

            I am unsure if this is a .Net version conflic with Datapump of if it is a bug so any help would be appreciated.

            • Script Help
              Gareth Horton

              Bill,

               

              At first glance I would say that this is likely to be a .NET version issue, as Data Pump uses .NET 1.1, so it should not be able to reference .NET 2.0 assemblies successfully.

               

              However, you mention that it was previously working, which although very mysterious, very little regarding .NET would surprise me these days.

               

              There is an issue which is only supposed to affect ALL scripting (not just Data Pump) in the .NET Framework 2.0 under Vista, but seems to have spread to other operating systems, due to .NET Framework updates for Exchange 2007, SQL Server 2005 and other apps.

               

              Here is the  [url="http://support.microsoft.com/kb/928208"]link[/url] for this.

               

              Note that this explains that .NET 1.1 apps are not affected (i.e. Data Pump 8.5), but I have little to go on here.

               

              This also affects Visual Studio, SSIS 2005 and some other Microsoft products.

               

              You'll have to contact Microsoft for the fix, and see if it resolves the issue.

               

              Gareth

               

              Originally posted by Bill Watson:

              Gareth

               

              I wondered if you could cast your eye over this problem we are having - a preexport script that was running okay now causes datapump process to fail.

               

              Objective:[/b]   To truncate a table in Oracle before exporting data to the table, I don’t want to

              overwrite the structure of the table and therefore require to empty the table before appending the Exported data.

               

              I’ve added a PreProcess script (shown below) that compiles when the Assembly and Import references are added, once the process is run it returns an Assembly Name Invalid error ?

               

              Assemblies:[/b]

               

              System.Data.OracleClient: C:WindowsMicrosoft.NetFrameworkv2.0.50727System.Data.OracleClient.dll

              System.Data: C:WindowsMicrosoft.NetFrameworkv2.0.50727System.Data.dll

               

              Imports:[/b]

               

              System.Data.OracleClient

              System.Data

               

              PreExport Script:

               

              [font="courier"]Dim Oraclecon As New OracleConnection("Password=xxxxx;User ID=xxxxx;Data Source=xxxx;")

              Dim sql As String = "TRUNCATE TABLE_NAME"

              Dim myCMD As New OracleCommand(sql, Oraclecon)

               

              Oraclecon.Open()

              myCMD.Connection = Oraclecon

              myCMD.CommandType = CommandType.Text

              try

              myCMD.ExecuteNonQuery()

              Catch myex As Exception

                  MsgBox(myex.Message)

              End Try

               

              Oraclecon.Close()[/font][/quote]When we test this we get Script Compiled without Errors Dialog. However when we run the process via Datapump we get the following:

               

              [font="courier"]- <joblog process="CB_TB" jobID="1038">

              - <events>

                <event time="2007-11-01T12:05:39">Created.</event>

                <event time="2007-11-01T12:05:41" alert="SystemAlertProcessFailed">Failed: Microsoft.Vsa.VsaException: AssemblyNameInvalid (0x80133005).</event>

                <event time="2007-11-01T12:05:41" source="distributor">Distribution started</event>

                <event time="2007-11-01T12:05:41" source="distributor" value="complete">Distribution completed</event>

                </events>

                <distribution />

                </joblog>[/font][/quote]The same script, assemblies and imports are removed and readded to the PreExport tab but now it doesn’t compile and shows an Assembly Name Invalid. The dialog shows: Error Compiling Script: Microsoft.Vsa.VsaException: AssemblyNameInvalid(0x80133005)

               

              I am unsure if this is a .Net version conflic with Datapump of if it is a bug so any help would be appreciated. /b[/quote]

              • Script Help
                Bill Watson

                Thanks Gareth!

                 

                Will investigate link.