Archive

Archive for the ‘SQL Integration Services’ Category

Power Query with SSIS OData Feed Publishing Components

November 6, 2014 Leave a comment

Power Query won’t be able to connect to Oracle / IBM DB2 database unless the driver is installed locally. In many cases, we cannot freely install the driver due to enterprise security policy and lots of other reasons. And to mass distribute the diver to client PCs is another big issue. SSIS Data Feed component removes this gap via exposing complex ETL as a single SQL Server database view which can be queried by Power Query directly. With SSISDB introduced in SSIS 2012, powerful auditing and performance logging can be enabled with the help of out of box reporting features as well as lots of nice CodePlex community projects.

In a recent project, I use this technic to enable a scenario that an end user is able to query a SQL Server database view which actually invoke an ETL job in real time and streaming data back to Excel. If you do have the Power BI subscription, you can even share the Power Query through Power Query Data Catalog and become searchable by colleagues. To take further, I build an ASP.NET as a query register and capture some metadata of the query. Then I use Varigence Mist (BIML IDE, a super powerful tool!) to dynamically generate the SSIS packages on the fly and deploy to server via Power Shell and create a view on top of it. It is truly awesome and I’m proud of what I achieved.

The only drawback is that the Project Deployment does not support incremental deployment at the moment. I wish in next SSIS release, the incremental deployment can be implemented so that we can add additional SSIS Data Feeds without redeploy the entire project.

FW: Slides from SSIS Performance Design Patterns | TechDays Hong Kong 2013

March 15, 2013 1 comment
Categories: BI, SQL 2012, SQL Integration Services Tags: ,

Excellent SCD2 loading performance by Alex Whittles

April 17, 2012 Leave a comment

Excellent SCD2 loading performance by Alex Whittles. http://lnkd.in/uHZhYX

Now in addition to use CDC or StreamInsight to handle real time loading scenario, T-SQL Merge will be my preferred method to load SCD2 in SSIS if no third-party software is allowed (Although I use extensively the MergeSCD component which is previously called KimballSCD component).

Categories: BI, SQL Integration Services Tags:

Use SSIS to access SAS data

I’ve spent some time to look for the products that can access the SAS data files (.sas7bdat format in particular) .

And I’ve found two:

  1. SAS to Windoes utility (http://www.savian.net/#Utilities ). It is free.
  2. CozyRoc just released New SAS Adapters for SSIS (http://www.cozyroc.com/blog/new-sas%C2%AE-adapters-and-price-change . It enables SSIS to be able to read and write to sas7bdat (SAS format) files. The cost is USD$2,499.95/Year.

Talking about Analysis Services (SSAS) Processing and Aggregations

November 2, 2009 Leave a comment

Dan wrote a great article of how to use SSIS to determine which partition needs to be processed and process it according to the result. 

Quote

Analysis Services (SSAS) Processing and Aggregations

Talking about Create New SSAS Partition dynamically: XML Task

November 2, 2009 Leave a comment

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 5 (of 5)

September 24, 2009 Leave a comment

The below is my sample code:

public void Main()

        {

            try

            {    

                string siteURL = Dts.Variables["User::siteURL"].Value.ToString();

                string DealershipReportsPath = Dts.Variables["User::DealershipReportsPath"].Value.ToString();

                string SP_FolderName = Dts.Variables["User::SP_FolderName"].Value.ToString();

                string SP_FolderPath = Dts.Variables["User::SP_FolderPath"].Value.ToString();

               

                string Server = ""<Your Server>";

                string Database = ""<Your Database>";

                string ConnectionString = "Data Source=" + Server + ";";

                ConnectionString += "Initial Catalog=" + Database + ";";

                ConnectionString += "Integrated Security=SSPI";

 

                SqlConnection connection = new SqlConnection();

                connection.ConnectionString = ConnectionString;

                connection.Open();

               

                SPSite site = new SPSite(siteURL);

                using (SPWeb web = site.OpenWeb())

                {

                    SPFolder currentFolder = web.GetFolder(SP_FolderPath);

                    //MessageBox.Show("currentFolder= " + currentFolder.Name);

 

                    for (int i = currentFolder.Item.RoleAssignments.Count – 1; i >= 0; i–)

                    {                    

SqlCommand myInsertCMD = connection.CreateCommand();

                        myInsertCMD.CommandText = "insert into dbo.SharePoint_FolderPermissionList (SP_FolderName, SP_FolderPath, SP_FolderPermission) values(‘" + SP_FolderName + "’, ‘" + SP_FolderPath + "’, ‘"+ currentFolder.Item.RoleAssignments[i].Member.Name.ToString() + "’)";

                        myInsertCMD.ExecuteNonQuery();

                        myInsertCMD.Dispose();

                    }

                }

 

                connection.Close();

                connection.Dispose();

            }

 

            catch (Exception)

            {

                //MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);

            }

 

            Dts.TaskResult = (int)ScriptResults.Success;

        }

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 4 (of 5)

September 24, 2009 Leave a comment

Steps 3 – Use a “Execute SQL Task” to retrieve the list

I just use this: select * from dbo.SharePoint_FolderList

Steps 4 – Use a “For Each Loop” to loop through each foler

Steps 5 – Within the for each loop, use a script component to extract permission and write to the SQL table

Add the reference to the Microsoft.SharePoint and add the following in your code

using Microsoft.SharePoint;

using System.Data.SqlClient; //This is required for "SqlConnection" 

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 3 (of 5)

September 24, 2009 Leave a comment

Now you can add the code. Below is the sample code I used.

public void Main()

        {

            try

            {    

                string siteURL = Dts.Variables["User::siteURL"].Value.ToString();

                string DealershipReportsPath=Dts.Variables["User::DealershipReportsPath"].Value.ToString();              

                               

                string Server = "<Your Server>";

                string Database = "<Your Database>";

                string ConnectionString = "Data Source=" + Server + ";";

                ConnectionString += "Initial Catalog=" + Database + ";";

                ConnectionString += "Integrated Security=SSPI";

 

                SqlConnection connection = new SqlConnection();

                connection.ConnectionString = ConnectionString;

                connection.Open();

 

                SPSite site = new SPSite(siteURL);

                using (SPWeb web = site.OpenWeb())

                {

                    SPList list = web.Lists[DealershipReportsPath];

                    SPQuery query = new SPQuery();

 

                    //Condition to check the item type is folder or not

                    query.Query = "<Where><Eq><FieldRef Name=’FSObjType’/><Value Type=’Lookup’>1</Value></Eq></Where>";

 

 

                    //Get all the items including subfolders from the list

                    //query.ViewAttributes = "Scope=’RecursiveAll’"; 

/*if you uncomment the above line,it will traverse all sub folders. At the

moment, it DOES NOT go to sub folders.*/

 

                    //Retrieve the items based on Query

                    SPListItemCollection items = list.GetItems(query);

 

                    //Get the name and Url for the folder

                    foreach (SPListItem item in items)

                    {

                        SqlCommand myInsertCMD = connection.CreateCommand();

                        myInsertCMD.CommandText = "insert into dbo.SharePoint_FolderList (SP_FolderName, SP_FolderPath) values(‘" + item.Name + "’, ‘" + web.Url + "/" + item.Url + "’)";

                        myInsertCMD.ExecuteNonQuery();

                        myInsertCMD.Dispose();

                    }

                }

 

                connection.Close();

                connection.Dispose();

            }

 

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);

            }

 

            Dts.TaskResult = (int)ScriptResults.Success;

        }

How to use SSIS to extract permission assigned to each folder within a SharePoint List – Part 2 (of 5)

September 24, 2009 Leave a comment

Basically, I create 2 tables: “SharePoint_FolderList” and “SharePoint_FolderPermissionList”.

CREATE TABLE [dbo].[SharePoint_FolderList](

      [SP_FolderName] [nvarchar](100) NOT NULL,

      [SP_FolderPath] [nvarchar](350) NOT NULL

)

 

CREATE TABLE [dbo].[SharePoint_FolderPermissionList](

      [SP_FolderName] [nvarchar](100) NOT NULL,

      [SP_FolderPath] [nvarchar](350) NOT NULL,

      [SP_FolderPermission] [nvarchar](100) NOT NULL

)

There are a few steps in the SSIS package:

Steps 1 – Truncate the above2 tables

Steps 2 – Write the folder list to the staging table (SharePoint_FolderList)

(Thanks to Shantha Kumar’s Retrieve all folders with sub-folders from SharePoint List. I took his code to retrieve folder list.)

As I run the package on MOSS Server locally, I use SPWeb. (If you want to run the package remotely to the MOSS Server, you have to modify my code to use the SharePoint web services. Check “Retrieving folders from document library”: http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/16a2d993-2f5e-4242-8e5a-451a78c064a3). In the script component, add the reference to “Microsoft.SharePoint” and add the following line: using Microsoft.SharePoint;