CODEWITHSUNDEEP
c#
nova.cp
nova.cp

Reputation: 465

C# using mssql2012 import wizard

Is it possible to use the mssql2012 import wizard for databases and tables withing the c# code? I wanted to write a program with which i can import databases and tables of an access db to a mssql db. Therefore, as a gui, the mssql2012 import wizard works fine, i just need to implement it in c# Code in my project. Are there any prebuilt classes in the .net framework?

Regards

Upvotes: 0

Views: 65

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131774

If you just want to import data into the database, use SqlBulkCopy. The import wizard does more than just send a batch of data to the server.

Actually, the Import wizard creates and executes an SSIS package with data transformation that extracts data from the source and sends it to the target. It doesn't do anything by itself. The last step of the wizard allows you to save the generated package and use it again, as you would with any other SSIS package.

You can load a saved package and execute it using the code provider in "Loading and Running a Local Package Programmatically". The sample code is very simple:

  string pkgLocation;
  Package pkg;
  Application app;
  DTSExecResult pkgResults;

  pkgLocation =
    @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
    @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
  app = new Application();
  pkg = app.LoadPackage(pkgLocation, null);
  pkgResults = pkg.Execute();

  Console.WriteLine(pkgResults.ToString());

If you want to create a new package each time, you can use the EzAPI library to create a package programmatically, save it and/or execute it. The MSDN blog contains a sample for creating a simple dataflow task:

public class EzOleDbToFilePackage : EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzFlatFileDestination, EzFlatFileCM>
{
    public EzOleDbToFilePackage(Package p) : base(p) { }
    public static implicit operator EzOleDbToFilePackage(Package p) { return new EzOleDbToFilePackage(p); }

    public EzOleDbToFilePackage(string srv, string db, string table, string file)
        : base()
    {
        SrcConn.SetConnectionString(srv, db);
        Source.Table = table;
        DestConn.ConnectionString = file;
        Dest.Overwrite = true;
        // This method defines the columns in FlatFile connection manager which have the same
        // datatypes as flat file destination
        Dest.DefineColumnsInCM();
    }

    [STAThread]
    static void Main(string[] args)
    {
        // DEMO 2
        EzOleDbToFilePackage p2 = new EzOleDbToFilePackage("localhost", "AdventureWorks", "Address", "result.txt");
        p2.DataFlow.Disable = true;
        p2.Execute();
        Console.Write(string.Format("Package2 executed with result {0}\n", p2.ExecutionResult));
    }
}

You should note though that if the generated default mappings aren't suitable for your task, you'll have to define each one separately in code.

Upvotes: 1

David Jiboye
David Jiboye

Reputation: 511

I will suggest you use Bulk Insert. This has already been managed into a .NET class SqlBulkCopy .

Upvotes: 1

Related Questions