Showing posts with label SSDT. Show all posts
Showing posts with label SSDT. Show all posts

Monday, November 6, 2017

SSIS Basics: Integration Services Catalogs

In the new Project deployment model for SQL Server Integration Services, a new SQL Server database is used to provide a central management and deployment of packages: SSIS catalog


What it does?

Is an special database called SSISDB where you store projects, packages related to the projects, environments (for example dev, test, QA and Prod) and variables related to each environment (for example a connection string). It also stores the execution history for further analysis.


How to access it

First of all, you need to install it since it is not added by default.

If you don't have it installed

 

Open the SQL Server Management Studio, and then locate the Integration Services Catalogs folder, and then right click in the folder, and select the Create Catalog option.





After that, a new window will appear, just enter the password you want, and the catalog will be created.


If you have it already installed

 

Just expand the folder and the SSISDB will be available:


Also a SSISDB database is created, this can be used to perform custom queries or monitoring.



How to configure it


Now we can deploy packages and create environments for each project.

To deploy a test package

 

You can use SSDT IDE to deploy a package directly to the catalog if you have it configure as Project deployment model.

In the project, right click and select the Deploy option.


Select your SQL Server instance where the SSISDB catalog is installed. In the Path field, select the folder where you want to install the package.


Finish the wizard and if everything is OK, the project will be deployed to the catalog successfully.


 We can expand the catalog again, and we can see that the test package is available now.

 

Create environments

 

Another feature of the SSISDB catalog, is to create environments, were you can store different parameters and configurations for the package, so you can select to which one do you want to execute it.




As you can see, for this particular example we have created an environment to store different connection strings



So now you have the basic knowledge to get rid of maintaining your packages in the filesystem, as you can have a centralized place to store and manage them with improved security.


Sources

https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

Thursday, September 7, 2017

Change Data Capture Tables



A Change Data Capture (do not confuse it with Change Tracking) is a solution for datawarehousing applications when you want to perform incremental data loads on DW databases.
This info is stored in special system tables created stored in each database where this option is set.

This option is set at database level and you must use these stored procedures to set this property on the affected database:


EXEC sys.sp_cdc_enable_db --Enable CDC
EXEC sys.sp_cdc_disable_db --Disable CDC

Then you enable it for an specific table using the following stored procedure, using the standard schema.table convention:


EXEC sys.sp_cdc_enable_table
       @source_schema = '<schema_name>'
       ,@source_name = '<table_name>'
       ,@role_name = '<role>'

For simple applications, these options are fairly enough. Once enabled you can use SSDT to handle the data to suit your needs, using the CDC Source and CDC Split Transformation inside a Data Flow task:

"Sample" usage


CDC Source validates that the table you select is CDC enabled, while CDC Splitter splits each operation based on the __$operation Column on the table.


References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/change-data-capture-tables-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-flow-components

Wednesday, September 6, 2017

Determining DTSX Package version

Sometimes, for any reason, you have to create a SQL Server job that executes a dtsx package given by someone else. You don't know what version of SQL Server Data Tools the developer use to create this package, so you have to target the right SQL Server version to run the package.

First of all, to determine the package version, open the dtsx file in any text editor (I personally use notepad++), and locate the DTS:Name="PackageFormatVersion" property, as you can see in this image:


 

This table can help you to determine the right version


If you are running a newer version of SSDT, you can save any specific package to run in an older SQL server version with the TargetServerVersion property on the package properties:




 
Sources:

https://docs.microsoft.com/en-us/sql/integration-services/install-windows/installing-integration-services-versions-side-by-side 

 https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanagerbase.targetserverversion.aspx 

 Thanks for reading!

 -Eduardo-

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...