Transfer data from SQL Server to Smartsheet by SSIS and c#

Smartsheet Software is a great tool to manage projects and tasks. Many times, user needs to export data from SQL Server and import into Smartsheet. Sometimes it is a regular job to import to smartsheet from SQL server tables.
To reduce the manual work and make it automated, we can use c# Smartsheet SDK and SSIS package.
First create a SSIS package and create a variable of Object type for data table. I have created a variable as dt.

create ssis package

Now, Drag a “Execute SQL Task” to get data from SQL. Create a connection with SQL Server and set up connection information to the “Execute SQL Task”. Thereafter, Write a query in SQLStatementSource property. Then right click the “Execute SQL Task” and click Edit to get task editor. From the left navigation in the task editor, select Result Set. Usually, you will find it empty. So please click “Add button” to add to the result set. Give 0 in the “Result Name” column. Give name of variable under variable name column along with prefix “user” because it is user defined. Now click OK.

excute sql task manager

By doing the above exercise, We are setting up result set, because it will be required to store data into data table, further to pass data to “Script Task”.

To continue ahead, Now drag the script task from toolbox. Right click the script task and click edit. You will get three options to select from left navigation of edit box. By default, the first option “Script” is already selected. Now Go to “ReadWriteVariables” section in the right and put the name of variable we defined in previous step.

script task editor

The above step will help to pass all the data to the script which we are going to write. Now click “Edit Script” button. This will open the code in another visual studio instance.
Further, In the solution explorer, under reference section, add reference of below libraries which is provided by C-Sharp Smartsheet SDK.

  • smartsheet-csharp-sdk.dll
  • RestSharp.dll
  • NLog.dll
  • Newtonsoft.Json.dll

Note: Please ensure that above assemblies are installed to GAC.

Once references are added, import the namespace we will be using as below:
using Smartsheet.Api;
using Smartsheet.Api.OAuth;
using Smartsheet.Api.Models;

Now we can use Smartsheet object to connect and insert data into smartsheet. All you need is Smartsheet ID and Smartsheet API Token. Below is the code:

string smartsheetAPIToken = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”;
long sheetID = xxxxxxxxxxxxxxxxx; //Smartsheet ID
Token token = new Token();
token.AccessToken = smartsheetAPIToken;
SmartsheetClient smartsheet = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
Sheet sheet = smartsheet.SheetResources.GetSheet(sheetID, null, null, null, null,null, null, null);

//Code to read data table exported from SQL:

OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = new DataTable();

//Loop through the data table.

foreach (DataRow dRow in dt.Rows)
{

//Code to read column name in Smartsheet i.e. Top Row

Row nRow = new Row();
foreach (Row topRow in sheet.Rows)
{
nRow = topRow;
}

//Define Cell and create object mapped with each column

Cell ID = getCellByColumnName(nRow, “ID);
Cell Name = getCellByColumnName(nRow, “Name”);
Cell Address = getCellByColumnName(nRow, “Address”);
Cell Email = getCellByColumnName(nRow, “Email”);

//Assign data to be inserted to each Cell

ID.Value = Convert.ToString(dRow[“ID”]);
Name.Value = Convert.ToString(dRow[“Name”]);
Address.Value = Convert.ToString(dRow[“Address”]);
Email.Value = Convert.ToString(dRow[“Email”]);

var cellsToAdd = new List();
cellsToAdd.Add(ID);
cellsToAdd.Add(Name);
cellsToAdd.Add(Address);
cellsToAdd.Add(Name);

//Add row to smartsheet

row = new Row.AddRowBuilder(true, null, null, null,
null).SetCells(cellsToAdd).Build();

smartsheet.SheetResources.RowResources.AddRows(sheetID, new Row[] { row });
}

Below is the function getCellByColumnName used in the above code.
public Cell getCellByColumnName(Row row, string columnName)
{
return row.Cells.First(cell => cell.ColumnId == columnMap[columnName]);
}

Once your done with the code. Please save and close the code window and click OK to script task. Now your SSIS package will look like below:

ssis package

Now execute the SSIS package. You will see that data will be inserted into the Smartsheet.
Let us know the next topic you want us to share the knowledge about. Happy browsing!

Related Posts

Leave a comment