The SQL Server SSIS Script Task provides great flexibility, especially if you are accustomed to developing with vb.net or c#.net. The SSIS Script task is a control flow task, then enables you to develop for SSIS using Visual Studio Tools for Applications (VSTA), and perform such actions as manipulate variable and connection values during package execution.
When you edit a SQL Server Script Task (right-click, Edit the script task object after dragging it on to the Control Flow canvas, and then click on the Edit Script button), you are provided with a useful commented example on changing a connection value:
ConnectionManager cm = Dts.Connections.Add(“OLEDB”);
cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”;
Connections are accessed through the Dts.Connections collection. Similarly, variables are edited through the Dts.Variables collection (e.g Dts.Variables[“variable_name].value = “ “; ). Remember, you must select the package variables you intend to read or write to before you can use them in your VSTA script. To select your package variables, select them from within the “ReadOnlyVariables” and “ReadWriteVariables” properties of the Script Task you are designing.
SQL Server Import & Export Wizard
Did you know? You don’t have to use BIDS to create a simple integration services package. If you are more used to navigating your way around SSMS (SQL Server Management Studio), from there you can use the SQL Server Import & Export Wizard.
To start the SQL Server Import & Export Wizard in SQL Server 2008 R2, simply start SSMS, right-click on a database, select Tasks, and then select “Import Data” or “Export Data”. The wizard will then take you through the steps to create a simply integration services package, including the selection of a data source and destination.
Compared to the flexibility of designing an integration services package through the SQL Server Business Intelligence Studio, using the wizard, you are not able to implement control flow precedence, are restricted to one and only one source and destination, and cannot use a source defined in another package. However, if you are just interested in quickly creating a simply package for moving some data from one place to another, then the SQL Server Import & Export Wizard may be a good choice.
SSIS Data Profiler Task
The Data Profiler Task is perhaps my favorite feature of SSIS because it enables you to analyze the data of any source in order to establish its characteristics. I use the profiler task during the data analysis phase in order to establish the true characteristics of the data to be imported into a data warehouse. The profiler can help you do such things as establish candidate keys, find out the range of values that exist within any particular source field, find out the percentage of values contained within a source field that are null, etc, etc. Essentially, the data profiler task enables me to eliminate any assumptions about the underlying data to be used in a project.
The Data Profiler Task is a control flow task in SSIS. To profile a data source:
1. Start the SQL Server Business Intelligence Development Studio (BIDS).
2. Create or open an existing Integration Services project.
3. Create or open an existing package, and then click on the Control Flow tab of the package.
4. From the toolbox, normally displayed on the left hand of the screen, drag a Data Profiler Task on to the Control Flow canvas.
5. Right click on the newly created Data Profiler task.
6. In the General tab, here we are going to specify a destination where our profile output will be stored.
7. Next, for this example, let’s create a “Quick Profile” of a data source by clicking on the “Quick Profile” button.
Note that the Data Profiler Task requires an ADO.NET connection to the data source to be analyzed.
8. Select or create a new ADO.NET connection to the data source that you wish to profile.
9. Select the type of profiles you wish to create: this selection includes:
– Column Null Ration Profile Request
– Column Statistics Profile Request
– Column Length Distribution Profile Request
– Column Value Distribution Profile Request
– Candidate Key Profile Request
As you can see, there are a number of obviously useful types of analysis that can be applied to the underlying data, and any or all can be appended to the output.
10. After completing your selection, click OK, and then click OK again to close the Data Profiler Task Edit dialogue.
11. Now we are ready to execute our Data Profiler Task. To do so, simply click the green play button. When complete the output of the Data Profiler Task will be created within the output Destination specified when you set up the task.
12. To view your output, you will now need to start the Data Profile Viewer application. To start the application click on Start, select Microsoft SQL Server 2008 R2, then select the Integration Services folder, and click on Data Profile Viewer.
13. From there, simply open and browse to your output folder. Note, unless you specified an XML file, the output will not be in XML, so to see the output of the Data Profiler task when browsing to it from the Viewer, make sure you are browsing for All File types.