Generating new GUID column value in SSIS

We have a table named product info in that Product ID is uniqueidentifier column and it is a primary key. We are getting the flat file which is having the product name list and we need to load it.

Below is the table structure.

CREATE TABLE Product_Info(                 product_ID uniqueidentifier NOT NULL primary key,                 Product_name varchar(100) NULL )

Below is the file

 

In SSIS we don’t have any transformation which directly generates the random generate the uniqueidentifier number. For generating the GUID number we need to use C# code.

Let’s see this demo.

I am taking DFT and taking the source as Flat file and configuring this.

   

Now I am taking a script component and selecting it as transformation.

 

Now we need to create one output column.

 

And setting data type as Uniqueidentifier.

Now we need to click on the edit script and write the C# code to generate the new GUID number.

 

Below is the C# code.

public override void Input0_ProcessInputRow(Input0Buffer Row)     {                  Row.Guidnumber = Guid.NewGuid();     }

Save and close this.

Now I am taking oledb destination and configuring it.

 

Now the package is ready for a run.

 

Before running this package records in the table.

 

Now running this package.

 

See the records in the table.

GUID value is generated successfully.

The module XXXXX depends on the missing object YYYYY the module will still be created; however it cannot run successfully until the object exists

I am creating an SP in which I am calling another SP but when I am creating this SP getting below error.

The module XXXXX depends on the missing object YYYYY the module will still be created; however, it cannot run successfully until the object exists

   

The message shows that the SP XXXX is created successfully but Object YYYY is missing. Many this issue occurs when the depending object does not exist in the database or we are calling the SP which we don’t have access.

In the given example I don’t have ‘P_Get_Emp_count_T’  SP in the database.

  

Now I am correcting the SP name and altering the SP.

  

Now SP is altered successfully.

The 'XXXXX (procedure name)' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead

I created one simple SP it is created successfully. There is no error with the SP it is working fine but it returns below message.

The 'XXXXX (procedure name)' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead

My sp was as below.

CREATE PROCEDURE [dbo].[P_Sql_Server_log_Audit] AS BEGIN declare @returnval int; BEGIN TRY                 insert into Sql_Server_log_Audit (User_Nm,Start_Time)                 values (SYSTEM_USER,CURRENT_TIMESTAMP) END TRY BEGIN CATCH SET @returnval=-1; END CATCH; Return  @returnval; END

This is simple SP which inset a record into the sql_server_log_audit custom table.

Before executing this SP see the records in the table.

 

Now I am executing this SP.

After execution 1 record is inserted successfully. Also, we are getting this message.

After analyzing this SP and fund that this message is due to only one reason – we have declared a variable inside the scope of the Stored Procedure and we have assigned the return value into the catch block. It means if any error occurs during inserting a record into the table, I have assign return value to -1.

In the try block, I did not assign value to the same procedure before we return the same variable. When any variable is not assigned any value it is by default assumed as a NULL. By Design stored procedures are not allowed to return the NULL value. Hence the value is automatically converted to zero (0) and returned.

We need to set the return value after declaring this variable. Now I am altering this SP and setting the default return value.

Records in the table.

 

Now I am executing this SP.

 

Message went. One new record inserted successfully.

See the records in the table.

Call function in execute sql task in SSIS

In this post, we will learn how we will call a user-defined function in execute sql task. I am creating a simple user defined function where I am passing the product ID and this function will return product details.

Read more about User defined function: User define function(UDF) in sql server

https://bageshkumarbagi-msbi.blogspot.com/2017/08/user-define-function-udf-in-sql-server.html

USE [AdventureWorksDW2012] GO CREATE FUNCTION [dbo].[ufnGetProductName] (@ProductID [int]) RETURNS varchar(200) AS BEGIN     DECLARE @ProductDetails varchar(200);       SELECT @ProductDetails ='ProductKey : '+ cast (ProductKey as varchar)+' and ProductAlternateKey : '                 +ProductAlternateKey +'  and EnglishProductName : '+EnglishProductName    FROM DimProduct     where ProductKey = @ProductID     IF (@ProductDetails IS NULL)         SET @ProductDetails = 'Product is not listed'         RETURN @ProductDetails END;

Function created successfully.

Now In SSDT I am taking execute sql task and connecting to the AdventureWorksDW2012 database.

                         

Now we need to create two variable one for storing the product details and another to pass the product ID.   

Now we need to do the parameter mapping.

My function return the product details so I am setting direction as Return Value.

Read ReturnValue: ReturnValuedirection in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/returnvalue-direction-in-execute-sql.html

Read: Differencebetween Stored Procedure and Function in SQL Server

https://bageshkumarbagi-msbi.blogspot.com/2016/06/difference-between-stored-procedure-and.html

Click ok.

To see the value in product details I am using the script task and writing the below C# code.

MessageBox.Show("Product ID : " + Dts.Variables["User::productID"].Value.ToString()+            Environment.NewLine+"Product details : " + Dts.Variables["User::productName"].Value.ToString());

    

Now I am running this package.

Now I am passing the production number which is not in the table.

   

Difference between Output Parameter and Result Set in execute sql task

Result Sets and output parameters are not the same.

Result Set

ResultSets are used to store a result of a Select query: It can be a one or more columns and it can be a single row or a full result set or XML. ResultSets are retrieved as ADO ResultSets and can be stored within variables. It may be

It is in General tab.

None: Insert, update, delete, create, drop or truncate

Single Row: store the single row only

Full result set: stored full result. Data type will be the object type variable.

XML: to store the XML type records.

Output Parameters or return value

Output Parameters are used to store some values that can be set any part of the SQL command. Parameters have the same concept of a SQL stored procedure parameters.

We will get it in the parameter mapping

  

Read more about:

Read: CallStored Procedure in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/call-stored-procedure-in-execute-sql.html

Read: Outputdirection in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/output-direction-in-execute-sql-task-in.html

Read: ReturnValuedirection in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/returnvalue-direction-in-execute-sql.html

ReturnValue direction in execute sql task in SSIS

For executing the SPs we are using Execute sql task.

Read: CallStored Procedure in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/call-stored-procedure-in-execute-sql.html

Read: Outputdirection in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/output-direction-in-execute-sql-task-in.html

Let’s see how we can receive the SP return value in execute task.

Here I am creating a simple SP in this we are returning the current identity value. E_ID is identity column.

Create Procedure dbo.P_Insert_Emp (@E_name varchar(50),  @E_mobile varchar(10),  @E_add   varchar(10),  @Number_of_Records int output ) AS Begin declare @r int;    insert into emp (E_name,E_mobile,E_add)    values (@E_name,@E_mobile,@E_add)    select @r=@@IDENTITY    SElect @Number_of_Records=Count(*) from emp    return @r  End;

Now I am taking the execute sql task and doing the configuration.

Below is the sql statement

exec ?=P_Insert_Emp ?,?,?,? OUTPUT

Before that I am creating the variable to store the values

Now go to the parameter mapping.

Keep in mind for receiving the return value we need to select Direction as ReturnValue.

Click ok.

To see the value of the output parameter I am taking script task.

  

In C# script I am writing below code to see the message.

 

Now package is read to execute

Before running this package records in the table.

 

6 records.

Now I am running this package

See the records in the table.

Output direction in execute sql task in SSIS

For executing the SPs we are using Execute SQL task.

Read: CallStored Procedure in execute sql task in SSIS

https://bageshkumarbagi-msbi.blogspot.com/2019/11/call-stored-procedure-in-execute-sql.html

Let’s see how we can use an output parameter in SP.

I am creating a simple SP with an output parameter.

Create Procedure P_Insert_Emp (@E_name varchar(50),  @E_mobile varchar(10),  @E_add   varchar(10),  @Number_of_Records int output ) AS Begin    insert into emp (E_name,E_mobile,E_add)    values (@E_name,@E_mobile,@E_add)    Select @Number_of_Records=Count(*) from emp End;

In this SP I am inserting a record in each run and getting the count of all records in the output parameter.

I am taking execute SQL task and creating the connection manager. For the input and output parameter, we need to create variables to store the value.

 

Before that, I am creating the variable to store the values

 

Now we go the parameter mapping.

 

Keep in mind for the output parameter we need to select Direction as Output.

Click ok.

To see the value of the output parameter I am taking script tasks.

 

In C# script I am writing below code to see the message.

Now the package is ready to execute

 

Before running this package records in the table.

3 records.

Now I am running this package

See the records in the table.