Dealing With Limited Rights In Biml

Kevin Feasel

2017-08-17

Biml

Shannon Lowder walks through a scenario where he wants limited rights to process metadata changes, separate from any data transfer:

My development environment has a local instance of SQL Server with AdventureWorks2014 on it.  I’m going to use that as my source.  I also created a database on this instance called BimlExtract to serve as my destination database.

To create a user that can only read the schema on the source system, I created a login and user named ‘Biml’.  I granted this user VIEW DEFINITION in AdventureWorks2014. I also added this user to the db_owner group in BimlExtract.  Now, this user can read the schema of the source, and create tables in the destination. I’ve included the T-SQL to set the permissions in Database Setup.sql.

Now, we’re ready to walk through the solution.

Click through for the solution and also a GitHub repo with all of Shannon’s code.

Related Posts

DRY With Biml

Cathrine Wilhelmsen shows how to use tiered Biml files to prevent repetition: Many Biml solutions start very simple, with just a single Biml file that generates a few SSIS packages. Most developers quickly see the need for a more complex solution for multiple sources. One way to reuse code and apply the Don’t Repeat Yourself software engineering […]

Read More

Generating Task Factory Dynamics CRM Loads With Biml

Meagan Longoria shows how to use Biml to generate SSIS packages which use the Task Factory Dynamics CRM source: I recently worked on a project where a client wanted to use Biml to create SSIS packages to stage data from Dynamics 365 CRM. My first attempt using a script component had an error, which I […]

Read More

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031