C # Call The SSIS package and read the DataReader target,

C # Call The SSIS package and read the DataReader target,

C # Two DLL files must be referenced when the SSIS package is called. (The specific location is searched on drive C. The paths provided by MSDN and Baidu are not correct)

Microsoft. SQLServer. ManagedDTS. dll

Microsoft. SqlServer. Dts. DtsClient. dll

This is an example on MSDN.Https://msdn.microsoft.com/zh-cn/library/ms136025%28v= SQL .120%29.aspx

In the MSDN example, only one DataReader is read, and an event cannot be added to this method.

The following code reads multiple DataReader

String pkgLocation = @ "Package1.dtsx"; // Package path DtsConnection dtsConnection; DtsCommand dtsCommand; IDataReader dtsDataReader = null; Package pkg; Application app; DataSet ds = new DataSet (); app = new Application (); pkg = app. loadPackage (pkgLocation, null); string dataReaderName = "DataReaderDest"; dtsConnection = new DtsConnection (); {dtsConnection. m_pkg = pkg; // dtsConnection. connectionString = st Ring. Format (@ "/FILE" "{0}", pkgLocation); // another connection method. If a Package is used, you can add an event // dtsConnection. open ();} dtsCommand = new DtsCommand (dtsConnection); dtsCommand. commandText = dataReaderName; List <string> reader = new List <string> () {"DataReaderDest", "DataReaderDest2 "}; // List <IDataReader> readers = new List <IDataReader> (); foreach (var plain text in reader) {dtsCommand. commandText = plain text; dtsDataReader = dtsCommand. executeReader (CommandBehavior. default); readers. add (dtsDataReader); Console. writeLine ("read {0}", plain text);} foreach (var dataReader in readers) {try {DataTable dt = new DataTable (); dt. load (dataReader); Console. writeLine ("Data row {0}", dt. rows. count); ds. tables. add (dt);} catch (Exception ex) {Console. writeLine (ex. message);} finally {dataReader. close () ;}} dtsCommand. dispose (); return ds;

The above code can read more than one DataReader, but it takes 30 seconds to read the second one after reading the first one.

dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.SingleResult);

The reason is that the ReadTimeout of DataReader in the package is set to 30000 milliseconds by default to 100 milliseconds. Set each DataReader.