Category - Microsoft .NET 5
In this article, we will review how to create a data flow task package of SSIS in Console Application with an example. Requirements Microsoft Visual Studio 2017 SQL Server 2014 SSDT Article Done with the above requirements? Let's start by launching Microsoft Visual Studio 2017. Create a new Console Project with .Net Core. After creating a new project, provide a proper name for it. In Project Explorer import relevant references and ensure that you have declared namespaces as below: using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime; using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper; To import above namespaces we need to import below refrences. We need to keep in mind that, above all references should have same version. After importing namespaces, ask user for the source connection string, destination connection string and table that will be copied to destination. string sourceConnectionString, destinationConnectionString, tableName; Console.Write("Enter Source Database Connection String: "); sourceConnectionString = Console.ReadLine(); Console.Write("Enter Destination Database Connection String: "); destinationConnectionString = Console.ReadLine(); Console.Write("Enter Table Name: "); tableName = Console.ReadLine(); After Declaration, create instance of Application and Package. Application app = new Application(); Package Mipk = new Package(); Mipk.Name = "DatabaseToDatabase"; Create OLEDB Source Connection Manager to the package. ConnectionManager connSource; connSource = Mipk.Connections.Add("ADO.NET:SQL"); connSource.ConnectionString = sourceConnectionString; connSource.Name = "ADO NET DB Source Connection"; Create OLEDB Destination Connection Manager to the package. ConnectionManager connDestination; connDestination= Mipk.Connections.Add("ADO.NET:SQL"); connDestination.ConnectionString = destinationConnectionString; connDestination.Name = "ADO NET DB Destination Connection"; Insert a data flow task to the package. Executable e = Mipk.Executables.Add("STOCK:PipelineTask"); TaskHost thMainPipe = (TaskHost)e; thMainPipe.Name = "DFT Database To Database"; MainPipe df = thMainPipe.InnerObject as MainPipe; Assign OLEDB Source Component to the Data Flow Task. IDTSComponentMetaData100 conexionAOrigen = df.ComponentMetaDataCollection.New(); conexionAOrigen.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; conexionAOrigen.Name = "ADO NET Source"; Get Design time instance of the component and initialize it. CManagedComponentWrapper instance = conexionAOrigen.Instantiate(); instance.ProvideComponentProperties(); Specify the Connection Manager. conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connSource); conexionAOrigen.RuntimeConnectionCollection[0].ConnectionManagerID = connSource.ID; Set the custom properties. instance.SetComponentProperty("AccessMode", 0); instance.SetComponentProperty("TableOrViewName", "\"dbo\".\"" + tableName + "\""); Reinitialize the source metadata. instance.AcquireConnections(null); instance.ReinitializeMetaData(); instance.ReleaseConnections(); Now, Add Destination Component to the Data Flow Task. IDTSComponentMetaData100 conexionADestination = df.ComponentMetaDataCollection.New(); conexionADestination.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"; conexionADestination.Name = "ADO NET Destination"; Get Design time instance of the component and initialize it. CManagedComponentWrapper instanceDest = conexionADestination.Instantiate(); instanceDest.ProvideComponentProperties(); Specify the Connection Manager. conexionADestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connDestination); conexionADestination.RuntimeConnectionCollection[0].ConnectionManagerID = connDestination.ID; Set the custom properties. instanceDest.SetComponentProperty("TableOrViewName", "\"dbo\".\"" + tableName + "\""); Connect the source to destination component: IDTSPath100 union = df.PathCollection.New(); union.AttachPathAndPropagateNotifications(conexionAOrigen.OutputCollection[0], conexionADestination.InputCollection[0]); Reinitialize the destination metadata. instanceDest.AcquireConnections(null); instanceDest.ReinitializeMetaData(); instanceDest.ReleaseConnections(); Map Source input Columns and Destination Columns foreach (IDTSOutputColumn100 col in conexionAOrigen.OutputCollection[0].OutputColumnCollection) { for (int i = 0; i < conexionADestination.InputCollection[0].ExternalMetadataColumnCollection.Count; i++) { string c = conexionADestination.InputCollection[0].ExternalMetadataColumnCollection[i].Name; if (c.ToUpper() == col.Name.ToUpper()) { IDTSInputColumn100 column = conexionADestination.InputCollection[0].InputColumnCollection.New(); column.LineageID = col.ID; column.ExternalMetadataColumnID = conexionADestination.InputCollection[0].ExternalMetadataColumnCollection[i].ID; } } } Save Package into the file system. app.SaveToXml(@"D:\Workspace\SSIS\Test_DB_To_DB.dtsx", Mipk, null); Execute package. Mipk.Execute(); Conclusion In this article, we have explained one of the alternatives for creating SSIS packages using .NET console application. In case you have any questions, please feel free to ask in the comment section below. RELATED BLOGS: Basics of SSIS(SQL Server Integration Service)
Microsoft Announces .NET 5 Microsoft announces that the next release after .Net core 3.0 will be .NET 5. .NET 5 will be the big release in the .NET family. You will able to use it to target Windows, Linux, Android, iOS, macOS, tvOS, watchOS, WebAssembly, and more. Microsoft will introduce new .NET APIs, language features, and runtime capabilities as a part of .NET 5. Microsoft intends to release .NET 5 in November 2020, with the first preview available in the first half of 2020. It will be supported with future updates to Visual Studio 2019, Visual Studio for Mac, and Visual Studio Code. .Net 5 Moves Ahead with .NET Core .NET Core Functionalities: Cross-platform implementation in any device Support all key platform features for .net core, .Net Framework, xamarin Open-source and community-Oriented Support with future updates to Visual Studio Code, Visual Studio 2019, Command Line Interface, and Visual Studio for Mac. Fast, Scalable and High Performance Side-by-side installation Support for platform-specific features like Windows Forms, & WPF on Windows Smarter Deployment & packages Small projects Files Three New major supports for developers Java Interoperability will be available on all Swift and Objective-C Interoperability will be supported on all multiple operating systems CoreFX will be extended to support static compilation of .Net, smaller footprints, and support for more operating systems. The Other Highlights Features Desktop Development with .NET 5 .NET 5 will come up with all key desktop development functionalities and libraries. WPF, Windows Forms, UWP, and xamarin are the four key desktop platforms. Mobile Development with .NET .Net 5 will continue to build cross-platform mobile apps for Android, iOS, tvOS, macOS, and watchOS platforms using Xamarin. Runtime and Language with .Net 5 Mono is the original cross-platform implementation of .NET. It started out as an open-source alternative to .NET Framework and transitioned to targeting mobile devices like Android and iOS devices popular. CoreCLR is runtime used as part of .NET Core. It has been primarily targeted at supporting cloud applications, including the largest services at Microsoft and now is also being used for windows desktop, IOT, and machine learning applications. Cloud Development with .NET 5 The major functionality of .Net 5 is Azure app Development. With the release of the latest version of .Net, developers will continue to develop software with Azure. Game Development with .Net 5 Visual studio 2019 and .Net 5 will support utility, a vital part of .Net gaming to develop games for mobile and other gaming platforms.
