Programmatically Save DTS Packages to Files

One of the things that has always bugged me about DTS is how difficult it was to transfer the DTS packages from SQL Server to structured storage (aka a file on the disk). In Yukon, DTS packages are always stored as files but in SQL Server 2000 they are stored in SQL Server by default. It’s possible to use the DTSRUN command to automatically save a DTS package in a file. So wrote a little script to generate the statements to save every DTS package as a file.

DTSRUN.EXE is the command line utility that lets you execute DTS packages. I use it regularly to scehdule DTS packages or run them from batch files. The basic syntax is something like:

DTSRUN /S ServerName /N PackageName /E

You provide it the server name and package name and use the /E to have it use a trusted connection and it will run the package. You can also use DTSRUN to run packages stored in a structured storage file:

DTSRUN /F FileName /N PackageName /E

A strucuted storage file can have many DTS packages stored in it thus you need to provide the package name also. The other switch we’re interested in is the /!X switch which tells DTSRUN not to execute the package. And as it turns out, if you provide both the structured storage file name and the SQL Server name, DTSRUN will save a copy of the DTS package in the structured storage file. That syntax looks something like this:

DTSRUN.EXE /S ServerName /E /N PackageName /F FileName /!X

Keep in mind that every time you run this statement it will append your package to the structured storage file so its probably a good idea to reset the file each time. Knowing this I constructured a little script to extract all the packages to a structured storage file. It looks something like this:

DECLARE @TARGETDIR varchar(1000)

SET     @TARGETDIR = 'C:\DTSTest\'

SELECT  distinct  

        'DTSRUN.EXE /S '

        + CONVERT(varchar(200), SERVERPROPERTY('servername'))

        + ' /E '

        + ' /N '

        + '"' + name  + '"'

        + ' /F '

        + '"' + @TARGETDIR + name + '.dts"'

        + ' /!X'

FROM    msdb.dbo.sysdtspackages P

CommandLine

--------------------------------------------------------------------------------------

DTSRUN.EXE /S L30 /E /N "Import Snitz #2" /F "C:\DTSTest\Import Snitz #2.dts" /!X

DTSRUN.EXE /S L30 /E /N "Import Snitz Data" /F "C:\DTSTest\Import Snitz Data.dts" /!X

. . . .

DTSRUN.EXE /S L30 /E /N "Script" /F "C:\DTSTest\Script.dts" /!X

This script creates the DTSRUN statements. I just copy these into a batch file and then run the batch file. You could easily create a cursor around this and run each statement through xp_cmdshell.

Hopefully this little script will help you get more value out of your DTS packages.

.NET Web Services Tutorial

NET Web Services Tutorial

Environment: .NET 1.0.3705, IIS 5.0

Introduction

Visual Studio .NET makes .NET programming simple and accelerates the development process. It hides a lot of repetitive and configuration details from the user and improves productivity. However, sometimes you would like to program for .NET without using VS .NET; for example, you want to learn .NET framework programming and do not have access to VS .NET or you want to know what is actually going on under the hood.
(continued)
Writing a Web Service in .NET using VS .NET very is easy. However, it is possible to write Web Services using the plain .NET SDK. I struggled a lot to write Web Services without VS .NET and tried to find help on the Net. There are a lot of examples available for writing Web Services using VS.NET, but you will rarely find any examples of writing Web Services using only the .NET SDK. This article is exactly for this purpose. It looks at Web Services development using the .NET SDK alone.

We will write and publish a simple web Service. We will also write two Web Service consumers: one Web-based consumer (ASP.NET application) and another Windows application-based consumer. Let’s start writing our first Web Service.

Writing a Web Service

Following is our first Web Service; it exposes two methods (Add and SayHello) as Web Services to be used by applications. This is a standard template for a Web Service. .NET Web Services use the .asmx extension. Note that a method exposed as a Web Service has the WebMethod attribute. Save this file as FirstService.asmx in the IIS virtual directory (as explained in configuring IIS; for example, c:\MyWebSerces).

FirstService.asmx
  <%@ WebService language="C" class="FirstService" %>

  using System;
  using System.Web.Services;
  using System.Xml.Serialization;

  [WebService(Namespace="http://localhost/MyWebServices/")]
  public class FirstService : WebService
  {
      [WebMethod]
      public int Add(int a, int b)
      {
          return a + b;
      }

      [WebMethod]
      public String SayHello()
      {
          return "Hello World";
      }
  }

To test a Web Service, it must be published. A Web Service can be published either on an intranet or the Internet. We will publish this Web Service on IIS running on a local machine. Let’s start with configuring the IIS.

• Open Start->Settings->Control Panel->Administrative tools->Internet Services Manager.
• Expand and right-click on [Default Web Site]; select New ->Virtual Directory.
• The Virtual Directory Creation Wizard opens. Click Next.
• The “Virtual Directory Alias” screen opens. Type the virtual directory name—for example, MyWebServices—and click Next.
• The “Web Site Content Directory” screen opens. Here, enter the directory path name for the virtual directory—for example, c:\MyWebServices—and click Next.
• The “Access Permission” screen opens. Change the settings as per your requirements. Let’s keep the default settings for this exercise. Click the Next button. It completes the IIS configuration. Click Finish to complete the configuration.

To test that IIS has been configured properly, copy an HTML file (for example, x.html) in the virtual directory (C:\MyWebServices) created above. Now, open Internet Explorer and type http://localhost/MyWebServices/x.html. It should open the x.html file. If it does not work, try replacing localhost with the IP address of your machine. If it still does not work, check whether IIS is running; you may need to reconfigure IIS and Virtual Directory.

To test our Web Service, copy FirstService.asmx in the IIS virtual directory created above (C:\MyWebServices). Open the Web Service in Internet Explorer (http://localhost/MyWebServices/FirstService.asmx). It should open your Web Service page. The page should have links to two methods exposed as Web Services by our application. Congratulations; you have written your first Web Service!!!

Testing the Web Service

As we have just seen, writing Web Services is easy in the .NET Framework. Writing Web Service consumers is also easy in the .NET framework; however, it is a bit more involved. As said earlier, we will write two types of service consumers, one Web- and another Windows application-based consumer. Let’s write our first Web Service consumer.

Web-Based Service Consumer

Write a Web-based consumer as given below. Call it WebApp.aspx. Note that it is an ASP.NET application. Save this in the virtual directory of the Web Service (c:\MyWebServices\WebApp.axpx).

This application has two text fields that are used to get numbers from the user to be added. It has one button, Execute, that, when clicked, gets the Add and SayHello Web Services.

WebApp.axpx
  <%@ Page Language="C#" %>
  <script runat="server">
  void runSrvice_Click(Object sender, EventArgs e)
  {
      FirstService mySvc = new FirstService();
      Label1.Text = mySvc.SayHello();
      Label2.Text = mySvc.Add(Int32.Parse(txtNum1.Text),
                    Int32.Parse(txtNum2.Text)).ToString();
  }
  </script>
  <html>
  <head>
  </head>
  <body>
  <form runat="server">
    <p>
        <em>First Number to Add </em>:
        <asp:TextBox id="txtNum1" runat="server"
             Width="43px">4</asp:TextBox>
    </p>
    <p>
        <em>Second Number To Add </em>:
        <asp:TextBox id="txtNum2" runat="server"
             Width="44px">5</asp:TextBox>
    </p>
    <p>
        <strong><u>Web Service Result -</u></strong>
    </p>
    <p>
        <em>Hello world Service</em> :
        <asp:Label id="Label1" runat="server"
             Font-Underline="True">Label</asp:Label>
    </p>
    <p>
        <em>Add Service</em> :
        & <asp:Label id="Label2" runat="server"
               Font-Underline="True">Label</asp:Label>
    </p>
    <p align="left">
        <asp:Button id="runSrvice" onclick="runSrvice_Click"
             runat="server" Text="Execute"></asp:Button>
    </p>
  </form>
  </body>
  </html>

After the consumer is created, we need to create a proxy for the Web Service to be consumed. This work is done automatically by Visual Studio .NET for us when referencing a Web Service that has been added. Here are the steps to be followed:

• Create a proxy for the Web Service to be consumed. The proxy is created using the wsdl utility supplied with the .NET SDK. This utility extracts information from the Web Service and creates a proxy. Thus, the proxy created is valid only for a particular Web Service. If you need to consume other Web Services, you need to create a proxy for this service as well. VS .NET creates a proxy automatically for you when the reference for the Web Service is added. Create a proxy for the Web Service using the wsdl utility supplied with the .NET SDK. It will create FirstSevice.cs in the current directory. We need to compile it to create FirstService.dll (proxy) for the Web Service.

  c:> WSDL http://localhost/MyWebServices/
                  FirstService.asmx?WSDL
  c:> csc /t:library FirstService.cs
  

• Put the compiled proxy in the bin directory of the virtual directory of the Web Service (c:\MyWebServices\bin). IIS looks for the proxy in this directory.
• Create the service consumer, which we have already done. Note that I have instantiated an object of the Web Service proxy in the consumer. This proxy takes care of interacting with the service.
• Type the URL of the consumer in IE to test it (for example, http://localhost/MyWebServices/WebApp.aspx).

Windows Application-Based Web Service Consumer

Writing a Windows application-based Web Service consumer is the same as writing any other Windows application. The only work to be done is to create the proxy (which we have already done) and reference this proxy when compiling the application. Following is our Windows application that uses the Web Service. This application creates a Web Service object (of course, proxy) and calls the SayHello and Add methods on it.

WinApp.cs
  using System;
  using System.IO;

  namespace SvcConsumer{
  class SvcEater
  {
      public static void Main(String[] args)
      {
          FirstService mySvc = new FirstService();

          Console.WriteLine("Calling Hello World Service: " +
                             mySvc.SayHello());
          Console.WriteLine("Calling Add(2, 3) Service: " +
                             mySvc.Add(2, 3).ToString());
      }
  }
  }

Compile it using c:>csc /r:FirstService.dll WinApp.cs. It will create WinApp.exe. Run it to test the application and the Web Service.

Now, the question arises: How can I be sure that my application is actually calling the Web Service? It is simple to test. Stop your Web server so that the Web Service cannot be contacted. Now, run the WinApp application. It will fire a run-time exception. Now, start the Web server again. It should work. Great, isn’t it?

References :http://www.codeguru.com/csharp/csharp/cs_webservices/tutorials/article.php/c5477

Siempat Nempu Hilir

Will come soon, just wait for update!!.

Pilkada Pak-pak Barat

will come soon, please be patient.

Pemilihan KPUD Humbas Syarat KKN

will come soon, please visit for same news in banyak tikus blog !

Sungai Kampar Simpang Langgam

will come soon, for the same news please visit everson blog in friendster.

ASP.NET Grid Paging

will come soon, thx for visiting !

Horas….. Sidikalang….!

Selamat datang di Sidikalang Blog. Adapun blog ini adalah fasilitas yang saya manfaatakan untuk menuliskan catatan-catatan kecil yang sering saya gunakan.

Blog ini adalah blog pertama saya, hitung-hitung sambil belajar ngeblog. Dan harapan saya dengan memesan tempat di wordpress ini saya akan terbiasa dengan percakapan-percakapan dan tulisan-tulisan formil sehingga akan berguna buat saya pribadi dan orang lain.

 Secara umum blog ini akan saya manfaatkan untuk menyimpan tulisan-tulisan saya dan teman-teman yang ingin berpartisipasi. Dimana pada tahan awal akan saya muat dengan yang berbau teknologi karena kebetulan saya bergelut di dunia ICT. Apabila membawa pengaruh yang positif ke habit saya, nanti akan saya kembangkan ke bidang budaya dan pendidikan.

Jadi mohon buat teman-teman yang ingin menuangkan kemampuan menulisnya, mari silahkan bergabung. Songo ni majo ate…..