SQL SERVER: SSIS - ActiveX Task

Today I am going to explain SSIS - ActiveX Task. Here I am just explain that how to write VB Script in this task, which will execute at run time. This ActiveX Task is mostly used when we  move SQL 2000 DTS package to SSIS. As I found that many developers had used ActiveX with DTS packages in SQL SERVER 2000. The ActiveX Script task provides a way to continue to use custom code that was developed using ActiveX script, until such scripts can be upgraded to use the more advanced features provided by the Script task. Let's take an example to easily understand how to use ActiveX Task with SSIS. 1. Select and Drag, ActiveX Script Task, from Container Flow Items to designer surface.   2. Now we need to write a script which will execute when SSIS package loads. To write a script, Right click on  ActiveX Script Task, which we dragged to Design surface. Click on "Edit.", you will get page as: 3. SSIS ActiveX Script Task - General. Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we design this task. 4. SSIS ActiveX Script Task - Script Task: This is main section where we need to write script. Language: We need to specify which script language we are going to use for this task, either VB Script, JScript etc..Here we are going to use VB Script language for this demo Script: Here we need to write a script. Click on ".." button located at right side. It will open a dialog to write a script, as shown in figure:   Let's write VB Script code inside to test this task. I write this script: function Main() Dim Stuff, myFSO, WriteStuff, dateStamp dateStamp = Date() Stuff = "SSIS Text ActiveX Task" Set myFSO = CreateObject("Scripting.FileSystemObject") Set WriteStuff=myFSO.OpenTextFile("D:\SQLYoga\SSIS ActiveX Task.txt", 8, True) WriteStuff.WriteLine(Stuff) WriteStuff.Close SET WriteStuff = NOTHING SET myFSO = NOTHING End function Save it and click on Parse, which will parse this script and let us know if there is any problem in this script. If It is on then its allowed to proceed with the next step. Expression: Use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes. That's it. Let's run task, by right click on task and click on Execute Task, as shown in following figure. You can either Execute Package by right click on Package name, from Solution Explorer.   Once you run it and if logged in user has write access to the given path, SSIS package will create file at that place. Now go to that given path and see file is created or appended with content that we want to write. That's simple to configure and use SSIS ActiveX Script Task within BIDS. Let me know if you have any questions in designing SSIS Package

SQL SERVER SSIS: BULK INSERT Task

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK. The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way. We can move data to SQL SERVER Table or View by BULK INSERT Task. Let see How to configure SSIS BULK INSERT Task: 1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface: 2. Configure connection: Right click on Task and click on Edit Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted. NOTE: The Bulk Insert task supports only OLE DB connections for the destination database. Define Format: We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement. Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step. 3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers. That's it. Now execute the package, you find that your Table/view is populated with data from provided text file. Why we have both BULK INSERT AND DATA FLOW TASK ? We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable. The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view. With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import. So, we can use either of these as per our requirements. Let me know if you have  any questions. Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)

SQL SERVER SSIS: Basic Example Of Data Flow Task

Today I am going to give Basic example of Data Flow Task in SSIS, import CSV file to SQL SERVER Database Many times user asked to import their data from their files. They might have data in Microsoft Access, Microsoft Excel, CSV File, Text Tile etc.. Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That is the power of SSIS. 1. Drag Control: Drag Data Flow Task from Toolbox, to Design interface 2. Configure Data Flow Task: Once you have Data Flow Task on Design surface, Double click on it, or Right click on task and click on "Edit", it will redirect you to Data Flow Tab, as shown in Figure 2:   As shown in above figure, SQL SERVER SSIS give features to import/export data from/to many formats. You can find the different formats that is supported by SSIS to import data on LEFT PANE. 3. Configure Source Connection: Here, I have selected "Flat File Source", as we need to import data from CSV File. Drag "Flat File Source" to design surface and right click on edit and select "Edit", you will get screen like:   As shown in figure, Click on "New", It will open a form which allows us to select file which we want to import to our Database. First, Enter connection manager name and description. Then, select a file which you want to import to database. Then, Select Locale and Code page as per your requirement, For this demo, I don't need to change anything. Then, we need to select Format of the file. SSIS provide us three formats: Delimited, Fixed Width, Ragged Right. We need to import CSV file (Comma Separated file), so I have selected "Delimited". Then, We need to specify Text Qualifier, Select based upon your requirements. Then, we need to specify delimiter for Header row, as we have CSV  file, I have selected CR LF, means new row for header. Then, SSIS also give us features to skip rows. By Header Rows to skip, it allows us to skip n number of rows. At Last, If there is Header in column names, we just need to select this checkbox, "Column names in the data row". So SSIS understands to set the first row as Header row. That's it for configure file, Now we need to specify delimiter for column, which comes on next section called "Columns"   As shown in figure, Now we need to specify delimiter for Rows and Columns. As we are going to import CSV file (comma separated file), Row Delimiter is "{CR}{LF}" and column delimiter is ",". NOTE: Also notice that, it display Column Names in Header, as we have checked, "Column Names in the First data row". This is the data that we are going to import to SQL Database. You can use Advanced and Preview tab, to configure each column and preview, respectively. Now once, configuration is done, we need to select OLE DB Destination, as we need to import it to SQL SERVER database. 4. Configure Destination Connection: Here, I have selected "OLE DB Destination", as we need to import data from CSV File to SQL SERVER Database. Drag "OLE DB Destination" to design surface.   Now, we need to design data flow. Here we need to import data from Flat File connection, I have dragged "Success" (Green Arrow), link to OLE Db Destination for column Mapping. Now, we need to configure OLE DB destination, to insert data to SQL Database. Right click on it and click on "EDIT", it will open screen like this:   I have entered SQL server Login information to connect to SQL SERVER where I need to import data from CSV file. Once, SQL SERVER is configured, we need to select Table Name to which we need to insert data from CSV file. See the following screen, I have selected "Employee" Table: Please find script to create "Employee Table" CREATE TABLE [dbo].[Employee](     [EmpID] [int] IDENTITY(1,1) NOT NULL,     [EmployeeName] [varchar](max) NULL,     [EmployeeCode] [varchar](max) NULL,     [EmployeeDesignation] [varchar](max) NULL,     [CreatedDate] [datetime] DEFAULT GETDATE() ) Now we have selected SQL SERVER table to which we need to import data, Now we need to map columns names, so SSIS will insert data accordingly. By default, SSIS provides mapping for columns which have same name, In this case Column Mapping is ok, so we don't need to do anything. If you want to change mapping, you can do it by just changing links between Input Columns and Destination Columns. NOTE: Here Destination table has two more columns, EmpID and CreatedDate, which is not available in source file, CSV file. We can just ignore it, as EmpID is Identity column while CreatedDate is GETDATE(). We have such columns in SQL database to keep track when this record is added. That's it. We have configured Data Flow Task. Now just right click on package and "Execute Package". You will find that Employee table will be populated with information from CSV file. Let me know if you have any questions in designing SSIS Package.

SQL SERVER: What is SSIS?

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server SSIS (SQL Server Integration Services) is introduced in SQL SERVER 2005. It is totally new transformation of data. Previously we have DTS package in SQL SERVER 2000. Now they introduced SSIS with many features. SSIS is an extensive data integration platform which is used to transfer and merge the information from various sources and load to multiple systems. It contains wide range of capabilities. Business Intelligence is used for decision making, future trends based on collected data. I can find very good article written by Microsoft SQL SERVER MVP, Pinalkumar Dave. He write about BI on his one of the best article: http://blog.sqlauthority.com/2009/10/21/sql-server-introduction-to-business-intelligence-important-terms-definitions/. SSIS packages can be created in BIDS (Business Intelligence Development Studio). I am going to explain all features of SSIS for easy and better understanding by following articles. Reference: Tejas Shah (http://www.SQLYoga.com )

Top features of Windows 7 and Office 2010

We have Community TechDays on 3rd October 2009, Saturday at Hotel Anmol, Ahmedabad. This was excellent event, you can find out more details here. I like these features of Windows 7 and Office 2010 which was presented by Vinod kumar, Microsoft Evangelist. 1. I like the Recorder Tool provided by Windows 7. This is very handy tool which will make our (IT professional) life easy. Many times we find that users complaint that application raise an error. What we need is, what user did so error occurs. By this tool, user can record all of the steps. This tool will capture all screen shots and description what user did. So user just need to send this file to us and we can easily come to know that why this happen. One more thing is that, File size is also not much though all screen shots are there. We can also have option to reduce size, by reducing quality of images. 2. I like the option of Jump List, We will access applications like windows media player just from Task Bar. We don't need to even open that Application. This is very nice feature as everything is just from task bar. 3. There is a feature to Magnify the screen and also point out the location. This we really need when we are giving presentation or in meetings. By this feature we can present things in easy way so all persons can understand easily. 4. Feature: Trigger Start Service. This is really good feature, as previously it was programmed as System keeps check periodically about the new hardware is attached or not. That was really resource consuming. Microsoft resolved this in Windows 7. As Vinod Kumar explained with USB drive at Community TechDays, Event is only fired when we attach any new hardware else it is not in even memory. 5. Now we can also Re-Arrange open applications in Task manager. As we used Tabbed browser and like to move important tabs first. This is the same concept developed in Windows 7 to arrange icons in Task Manager. 6. Show Desktop feature, Windows 7 introduced the "Show Desktop" button at right bottom side. We just need to click on right bottom which will minimize all applications which will  make developers life simple :) 7. Windows 7 also comes with good display options. We can customize the color of Taskbar icon's color. 8. Virtulization, The most common reason is that you want to run Virtual PC on Windows 7 on a system that does not have hardware virtualization support. 9. In Office 2010, feature to notify icon which indicates that recipient is available or not at that time. By this feature, at the time of sending an email come to know that recipient is available or not. 10. Also in Office 2010, Grouping follow up mails. This is feature by which we can find whole list mails, received and sent. So we come to know about the discussion from first email to last email. 11. In Office 2010, You can not copy the copyright contents. I can not get correct name to explain this. There are many more featured explained by Vinod Kumar @ Community TechDays. Here I just mentioned features those are still on my  mind and I like this in real life. Reference: Tejas Shah (http://www.SQLYoga.Com)

Community TechDays at Ahmedabad - Great Event

There was a great event held by Microsoft and PASS (Professional Association of SQL Server) in Ahmedabad on 3rd October 2009. This was very big event where 250+ attendees attend the excellent session of Vinod Kumar, Pinal Dave, Jacob Sebastian and Prabhjot Singh Bakshi. There were Four technical sessions by these excellent speakers. First session is of Vinod Kumar. He is Microsoft Evangelist. There was excellent session of Three hours where he give features of Windows 7 and Office 2010. Second session is of Pinal Dave, SQL MVP. He presented session on the subject - "SQL Server - The Other Side of Index". This was also excellent way to know more about SQL Index. and How to use index. That is really nice session for SQL Developers to know, How to optimize query. It gives proper understanding of indexes. Third session is of Jacob Sebastian, SQL MVP. He presented session on '"The Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server". This was also very great session where developer can get more idea about When exception occur and how to handle that exception. It was presented in a way so all attendees get clear idea about it and understand it properly. Forth and last session is of Prabhjot Singh Bakshi, MCT(Microsoft Certified Trainer). He presented session on "NET Framework 4.0". He also presented very nice session about featured of .Net Framework 4.0. He developed .Net code to give clear understanding of new features. Click with Vinod kumar and Jacob Sebastian: Overall, that was excellent day for Ahmedabad. I hope this type of TechDays scheduled in near future. Reference: Tejas Shah ( http://www.SQLYoga.com)