Home > SSIS > SSIS TIP #2: Table Variable Output with NOCOUNT ON

SSIS TIP #2: Table Variable Output with NOCOUNT ON

When we using TABLE VARIABLE as source in the SSIS, we must set NOCOUNT ON.

In preview of the rows it can show the data rows, however when we execute it will not give any data.

Let’s check in SSIS.

Create one DFT in SSIS package and use the OLE DB Source and write down the below query.

 DECLARE @abc TABLE (Empid INT ,Name VARCHAR(50))

INSERT INTO @abc VALUES(1,’prasad’)

SELECT * FROM @abc

In below screen shows the Source query in OLEDB Source task.

OLE DB SOURCE

Try to Preview the rows by clicking the Preview… Button. It will show data as shown in the below screen.

PreviewSource

 

 

So we are getting data in preview. Lets run the package before that add the Union all component and data viewer to see the data flow between Source to destination.

NoDataRows

In above screen we can see there is no row passed between source to destination.

Lets add NOCOUNT ON in the source query as given below:-

SET NOCOUNT ON

DECLARE @abc TABLE (Empid INT ,Name VARCHAR(50))

INSERT INTO @abc VALUES(1,’prasad’)

SELECT * FROM @abc

SET NOCOUNT OFF

After above change, once again execute the package. Below screen shows the execution result

DataRows

Hence After using NOCOUNT ON we can see data in data viewer and number of rows in front of precedence constraint.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSIS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: