Advertisements

Archive

Posts Tagged ‘SSIS’

[Step by Step] Connecting to Azure SQL Server using OLEDB Connection from SSIS

Connecting to Azure SQL Server from SSIS using ‘OLEDB Connection manager’  is not a straight forward way, if you are to connect using your ‘Azure Account’ credentials.

The only way to connect using ‘Azure Account’ is by using ‘ADO.NET connection’ manager.

Azure SQL_6

But then, how to connect to Azure SQL using OLEDB? Answer is by using ‘SQL User’. And yes, we need to create ‘SQL User’ on the Azure DB which you are connecting to and use the same in SSIS OLEDB.

Below are the steps to create ‘SQL User’ and use that to connect to ‘Azure SQL Server’ from SSIS OLEDB.

Create a ‘SQL Login’ on Azure SQL Server:

  • Connect to Azure SQL Server using SQL Server Management Studio (SSMS) using the Azure Account. ‘Azure Account’ must be Administrator to ‘Azure SQL Server’

Azure SQL_1

  • Select ‘Master’ Database and open a ‘New Query’ window.
    • Note that, ‘SQL Login’ creation query must only run on ‘Master’ database
  • Create a ‘Login Account’ using below query. In my query I am creating a Login Account by name ‘MyLogin

Azure SQL_2

Create a ‘SQL User’ on required Databases:

  • Post creation of ‘SQL Login’, now we have to create ‘SQL User’ account against all the Databases, which you want access from SSIS OLEDB.
  • From the SSMS -> Object Explorer, select the Database and open a ‘New Query’ window.
  • Create a ‘SQL User’ using below query. In my query I am creating a SQL User by name ‘MyUser’

Azure SQL_3

  • Add ‘db_datareader’ and ‘db_datawriter’ roles to the ‘MyLogin’ Login Account using below queries.

Azure SQL_4

  • Note: You can combine and run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries together.
  • Also, run ‘Create SQL User’ and ‘Grant Data Reader and Writer Roles’ queries against ‘Master’ Database as well.
  • Refer this article for list of ‘Roles’

Connect to Azure SQL from SSIS OLEDB:

As we have ‘SQL User’ created and granted access against the required databases, to connect to Azure SQL from OLEDB.

  • Open the ‘OLEDB Connection Manager’
  • Set ‘Server name’ to ‘Azure SQL Server’ name
  • Set ‘Authentication’ to ‘SQL Server Authentication’
  • Set ‘User name’ and ‘Password’ to ‘SQL User’ credentials created in above section

Azure SQL_5

🙂

Advertisements
Categories: CRM, SQL Tags: , , ,

SSIS – Read C# ‘List’ collection in ‘Execute SQL Task’

In one of our integration requirements, we had to read the ‘List<>’ collection values, which was set in ‘Script Task’ control and pass to ‘Execute SQL Task’ control.

In this article I am going to list out the step by step process to set and get the ‘List<>’.

Assume that you have a collection List<string> which holds ‘Product Names’

var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};

Lets see how to loop through and read the ‘Product names’ and pass to ‘Execute SQL Task’.

Required SSIS components:

  • A SSIS ‘Variable’ (i.e., varProducts) of type ‘Object’ to set the ‘List<>’ object in ‘Script Task’ control

SSIS_1

  • A SSIS ‘Variable’ (i.e., varProductName) of type ‘String’ to set the ‘Product Name’ for each List<> value.

SSIS_2

  • ‘Foreach Loop container’ to parse and read the ‘List<>’

Steps:

  • Choose ‘varProducts’ as ‘ReadWriteVariables’

SSIS_3

  • In ‘ScriptTask’ set ‘listProduct’ object to ‘varProducts’ variable.
    • var listProducts  = new List<string>() {“Product1″, ” Product2″, ” Product3″};
    • Dts.Variables[“User:: varProducts”].Value = listProducts ;
  • In ‘Foreach Loop container’,
    • Choose ‘Enumerator’ as ‘Foreach From Variable Enumerator’
    • Enumerator Variable as ‘User:varProducts’

SSIS_4

  • As the ‘List’ contain ‘Product Name’ collection, read the ‘Product Name’ to ‘varProductName’ variable. ‘Index’ 0 denotes, first value in collection (i.e., Product Name’.

SSIS_5

  • As the final step, In ‘Execute SQL Task’, map the ‘varProductName’ variable to a ‘Parameter’.

SSIS_6

  • In above screen, we are setting ‘@prodName’ parameter with ‘Product Name’ and ‘@prodName’ further used in ‘SQL Statement’ of ‘Execute SQL Task.

🙂

Categories: SQL Tags: ,