Tag - Efficiency

Create SSIS Data Flow Task Package Programmatically
Jul 27, 2020

In this article, we will review how to create a data flow task package of SSIS in Console Application with example. Requirements Microsoft Visual Studio 2017 SQL Server 2014 SSDT Article  Done with the above requirements? Lets start by launching Microsoft Visual Studio 2017. Create a new Console Project with .Net Core.  After created new project provide proper name to 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.

Top 6 powerful, compact and well-organised tasks to increase your productivity
Feb 12, 2020

Productivity is the grouping of smart preparation and dedicated hard work. Being continuously productive in your work is hard. End of each working day, there are chances are that you are not satisfied with what you have been gifted. Productivity could be constantly value-added, but here are 8 pointers that can have truly work for you. Fortunately, a computer, smartphone, and a little know-how are all you need. 1. Speed Reading Saves Your Time. In your daily working schedule, you might have to read huge passages of text quickly, think about figuring out how to speed read. Under normal circumstances, a person reads around 200 to 250 words per minute (WPM) and this is slow compared to speed readers because of sub-vocalization – you unconsciously read every word to yourself as you read. Eliminating sub-vocalization is one of the essential fundamentals of speed reading. You can use Speed Reader (by P Garrison) app for your android phone and tablet, for google chrome you’ll use Spreed extension and for iOS try Speed Reading HD. These apps are used Rapid Serial Visual Presentation technique. In this technique, you’ll see one word at a time very quickly in the center of your screen. You’ll observe that you’re rapidly capable to read 300wpm and with a little more practice you’ll reach 500-600wpm. You’ll have the capacity for reading three times quicker with a couple of days of practice. 2. Take a Break Reminder Every day we are working hard sitting in front of the computer for quite a long time. But we never distinguish that it is so critical to step away from the desk and take a break. Break empowers us to work more proficiently and it will improve our physical and mental health as well. A familiar way to take a break without connecting anything is the use of an online timer atwww.onlineclock.net. Go to this website and set your comfortable and right time to remind you about the break. Your device must be attached to the speakers to hear sounds. Smartphone users are also connected with the help of applications. Android devices users use Countdown Time Widget, Blackberry users use Countdown Timer app and iPhone users use Simple Repeat Timer applications. 3. Accelerate Your Computer and Handy Devices Remember that time is money. If your computers, mobiles and other devices are running slow, you’ll drop your extra valuable time for the small stuff. You can add those extra minutes to your productivity when your gadgets are working properly and promptly. Glary Utilities is a free, powerful and all-in-one utility for cleaning your Windows PC. For Mac use OnyX to delete caches, remove a certain number of files and folders that may become cumbersome and more. Clean Master is a free optimizer android app, ultimately it is all about increasing phone performance, deleting junk and spam files and protecting against trojans, viruses, and malware. 4. Synchronize Your Phone and Computer to get Notifications on your screen We all suffer from a bad experience to want something from the computer to phone. Get all notifications from computer to phone and vice versa here is best all in one application called Pushbullet. Pushbullet is quick, simple and works the way you need it to. Pushbullet is available for android, iPhone, Google Chrome and Mozilla Firefox. When you send a document with Pushbullet, its’ naturally downloaded so you can open it right from your notifications. 5. Set Up an Automated Systematic Backup Process You are working on something and you didn’t get backup of those files means your data is not secure. So, you must have set a backup system. There are two types of getting data backup, online data backup, and offline data backup. You can use the external hard drive to get offline data backup and for online backup, there are many data storage and synchronization services are available. With the help of SyncBack and Carbonitecloud backup service, you can set your automate data backup process. SyncBack is a really good software for backup and synchronizing the files to your targeted drive and also you’ll get an external backup to CD, DVD, and more devices. Carbonite is a cloud backup service that takes backup of your documents, music files, images, emails and many more. You’ll also use Google Drive. Google Drive allows you to store up to 15GB data as complimentary. 6. Learn Common Keyboard Shortcuts Keyboard Shortcuts can help you to increase your work speed. Let’s take an example, I want to print my document. For the print I click on the file menu, then click on the print option and then I show pop-up box for print or simply press Ctrl + P, so using of keyboard shortcut will save time. Many keyboard shortcuts enable us to complete our work easier and save time. You’ll create a note of shortcut keys to your software and print on paper and stick to your desk, whenever you want some specific work with your software check shortcut key and get your work done.