In the previous part, Part 1 we configured the ADO.NET Source to get the data from the local instance of SQL Server 2012.
In this post we will configure a Recordset Destination to get the input of data from the above source. We will also configure how much of the data from the source should be sent to the Recordset Destination.
Recordset Destination is an object which stores the information it receives in memory and is characterized by a variable of type Object. After the recordset saves data you will have to use Foreach Loop container or some other way to read the data.
Let us start inserting the Recordset Destination.
Adding a Recordset Destination
Click Recordset Destination under Other Destinations in the SSIS Toolbox shown here. Hold it and drag it over to the Data Flow tab of the Packet Designer (Package.dtsx[Design]).
ADONET_dec28_01
After dropping Recordset Destination the designer appears as shown.
ADONET_dec28_02
Configuring the Recordset Destination
You notice the red circle with x in it. This means this object is not configured.
Hold and draw the dangling(arrow pointing downwards on the ADO.NET Source, click on an empty area) to touch the Recordset Destination and let it go. The source ADO.NET Source and the Recordset Destination are now connected solidly and the Recordset is ready to take in the data from the source.
ADONET_dec28_03
Right click the Recordset Destination to display the list shown:
ADONET_dec28_04
Click Edit...
The Advanced Editor for Recordset Destination gets displayed a shown.
ADONET_dec28_05
In the Component Properties tab go down the list and provide a variable name for this object, let say X ( we may have to makes changes to this later). You may accept others as is (in the scope of the present post).
ADONET_dec28_06
Click on Input Columns tab on the Advanced Editor.
The columns coming from the ADO.NET Source are shown in the top pane. The error at the bottom is saying that you should at least choose one column.
ADONET_dec28_07
Place check marks for CategoryID, CategoryName and Description columns in the top pane. They get shown in the bottom pane.
ADONET_dec28_08
The bottom pane has three columns, The Input Column, the Output Alias (if you want you can change the alias by overwriting) and the Usage Type set to ReadOnly. If you are making changes you can change this to Read/Write.
Now click the Refresh button. The message goes away and you are now in the clear.
Click OK on the Advanced Editor window.
When you come back the Recordset Destination is still showing the red icon. Although you associated the Recordset with a variable, it is not associated with the package.
Right click an empty area (as shown) in the designer to bring up a pop-up pick list as shown.
ADONET_dec28_09
Click Variables.
The Variables window is displayed as shown.
ADONET_dec28_10
Click the first icon at the top (hovering over it will show Add Variable).
The Variable window changes to the following:
ADONET_dec28_11
The name is the same one we gave to the Recordset and the data type is Object and Data Value is System.Object.
ADONET_dec28_12
The object name X given is not completely defined. Go back to the Advanced Editor and click on the Variable Name drop-down. Choose the notation User::X as shown. Click OK
ADONET_dec28_13
Now the error on the Recordset Destination is gone. Now you are ready to execute. If you do not see the columns, do not worry as the data is in the memory.
Executing the Task
It is time to test the Data Flow Task we have configured. Click on an empty area in the designer and click Execute Task.
This may take some time. The design area becomes blank and after a little while you will see the following:
ADONET_dec28_14
Well where are the rows from the source transferred to the Record Set?
We will do it in another post.
For now Click on the Progress in the previous image. We see that 8 rows were copied over to the Recordset Destination.
ADONET_dec28_15
This completes this post. In the next we look at displaying the rows copied.
In this post we will configure a Recordset Destination to get the input of data from the above source. We will also configure how much of the data from the source should be sent to the Recordset Destination.
Recordset Destination is an object which stores the information it receives in memory and is characterized by a variable of type Object. After the recordset saves data you will have to use Foreach Loop container or some other way to read the data.
Let us start inserting the Recordset Destination.
Adding a Recordset Destination
Click Recordset Destination under Other Destinations in the SSIS Toolbox shown here. Hold it and drag it over to the Data Flow tab of the Packet Designer (Package.dtsx[Design]).
After dropping Recordset Destination the designer appears as shown.
Configuring the Recordset Destination
You notice the red circle with x in it. This means this object is not configured.
Hold and draw the dangling(arrow pointing downwards on the ADO.NET Source, click on an empty area) to touch the Recordset Destination and let it go. The source ADO.NET Source and the Recordset Destination are now connected solidly and the Recordset is ready to take in the data from the source.
Right click the Recordset Destination to display the list shown:
Click Edit...
The Advanced Editor for Recordset Destination gets displayed a shown.
In the Component Properties tab go down the list and provide a variable name for this object, let say X ( we may have to makes changes to this later). You may accept others as is (in the scope of the present post).
Click on Input Columns tab on the Advanced Editor.
The columns coming from the ADO.NET Source are shown in the top pane. The error at the bottom is saying that you should at least choose one column.
Place check marks for CategoryID, CategoryName and Description columns in the top pane. They get shown in the bottom pane.
The bottom pane has three columns, The Input Column, the Output Alias (if you want you can change the alias by overwriting) and the Usage Type set to ReadOnly. If you are making changes you can change this to Read/Write.
Now click the Refresh button. The message goes away and you are now in the clear.
Click OK on the Advanced Editor window.
When you come back the Recordset Destination is still showing the red icon. Although you associated the Recordset with a variable, it is not associated with the package.
Right click an empty area (as shown) in the designer to bring up a pop-up pick list as shown.
Click Variables.
The Variables window is displayed as shown.
Click the first icon at the top (hovering over it will show Add Variable).
The Variable window changes to the following:
The name is the same one we gave to the Recordset and the data type is Object and Data Value is System.Object.
The object name X given is not completely defined. Go back to the Advanced Editor and click on the Variable Name drop-down. Choose the notation User::X as shown. Click OK
Now the error on the Recordset Destination is gone. Now you are ready to execute. If you do not see the columns, do not worry as the data is in the memory.
Executing the Task
It is time to test the Data Flow Task we have configured. Click on an empty area in the designer and click Execute Task.
This may take some time. The design area becomes blank and after a little while you will see the following:
Well where are the rows from the source transferred to the Record Set?
We will do it in another post.
For now Click on the Progress in the previous image. We see that 8 rows were copied over to the Recordset Destination.
This completes this post. In the next we look at displaying the rows copied.
No comments:
Post a Comment