Using DTS in C # to import data and related issues

Import Data | problem

Use the DTS features of MS SQL when you import Excel data into SQL
Can be easily imported, while referencing DLL files, you can control the import process in the program.

The process of creating a DTS package is as follows:
1. In SQL Enterprise Manager, the Tools menu chooses Data Transformation Services, importing data
Following its graphical wizard prompts, you can create a DTS package step-by-Step and optionally save it in a database.
2. In SQL Enterprise Manager, select the Data Transformation service in the left tree structure, in the local package
You can create a new package, as well as a graphical interface to create a package: Drag an Excel data source, drag a SQL data source
Then select the Data Transformation task in the task, the original data selected Excel source, the purpose of the SQL, and then on the transfer of the shoulder to select the attributes
The transformation process can be configured inside. Note the data table with the primary key, to enable the insertion of the mark in the option to remove

The procedure is invoked in C # as follows:
private void Rundts ()
2 {
3 DTS. Package2class package = new Package2class ();
4 object p = null;
5 package. LoadFromSQLServer ("ws-victor-03", NULL, NULL, DTS.) Dtssqlserverstorageflags.dtssqlstgflag_usetrustedconnection,
6 null, NULL, NULL, "Testdts", ref p);
7 package. Connections.item (1). DataSource = "DTS. XLS ";
8 package. Execute ();
9}

There are problems with the project now:
1. Data that does not exist in Excel data sources, such as insertion time, when you insert data
2. The data in an Excel data source needs to be transformed, like a numerical form, and then turned into 1 after the import. 23131E12 Other Forms
3. Inserting data requires querying data from SQL and inserting

The

Now resolves the first and third steps:
When you configure the Data Transformation task above,

DTSDestination ("ICC") = DTSSource ("SIM number");
    DTSDestination ("IMSI") = DTSSource ("IMSI");
    DTSDestination ("PIN1") = DTSSource ("PIN1");
    DTSDestination ("PUK1") = DTSSource ("PUK1");
    DTSDestination ("PIN2") = DTSSource ("PIN2");
    DTSDestination ("PUK2") = DTSSource ("PUK2");
    DTSDestination ("MSISDN") = DTSSource ("MSISDN");
    dtsdestination ("fax") = DTSSource ("fax");
    DTSDestination ("data") = DTSSource ("Data");
    DTSDestination ("barring") = DTSSource ("barring");
    DTSDestination ("Mailformid")   = DTSGlobalVariables ("Mailfromid"). Value;
    DTSDestination ("receivedate") = new Date (). getyear () + "-" + new Date (). GetMonth () + "-" + new Date (). GetDate ();
    return (DTSTRANSFORMSTAT_OK);
where Mailfromid is a global variable and the Add method selects a global variable in the Package menu property, which can be assigned in C #:p>

foreach (GlobalVariable Global in package. GlobalVariables)
{
if (Global. Name = = "Mailfromid") {
Package. Globalvariables.remove (Global. Name);
}
}
Package. Globalvariables.addglobalvariable ("Mailfromid", Mailfromid);

Insert Time:
DTSDestination ("receivedate") = new Date (). getyear () + "-" + new Date (). GetMonth () + "-" + new Date (). GetDate ();
Spell it out with JavaScript, haha.

Inserting data requires querying the data from SQL and inserting it:
To add a new task to the package:
Execute SQL Task
SELECT addresslist.*
From AddressList
WHERE (ID =?)
Then configure input and output parameters

I still have the 2nd one left, I hope we can fix it in 2 days.