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;

        }