Biml Express - Enginyeria inversa

Ha sortit una nova versió de BIML Express per visual studio amb una novetat molt important:
    Added the Import Packages window to enable users to import existing SSIS packages and projects and convert them to Biml
   
   
Ja podem fer enginyeria inversa de les nostres ETLs, una característica que només estava disponible via pagament.
Això facilita la programació dels BIMLs, ja que podem crear l'ETL en SSIS, fer l'enginyeria inversa i després afegir-li la lògica en C# per poder automatitzar la creació de l'ETL segons els paràmetres que necessitem.

Link per descarregar.
https://www.varigence.com/BimlExpress

BIML Express 2018

Fa uns 2 anys que vaig començar el bloc centrant-me sobretot en BIML. En aquell moment la millor manera de programar BIMLs era a través del BIDS Helper. El problema que tenia el BIDS Helper era que l'interfície a l'hora de veure el codi BIML era molt justeta. Quan posaves codi c# emmig del codi BIML ja ho detectava com a error, les identacions no funcinaven del tot bé, etc.
Des de Varigence van treure el BIML Express que s'integrava amb el Visutal studio i millorava, sobretot, els temes d'intefície. Però la vida no era bonica del tot ja que no funcionava amb el Visual Studio 2017 ( que petardeja bastant en altres temes com el TFS, però això és una altra història).
Finalmen ha sortit la versió 2018 de BIML Express que ja funciona correctament per VS2017. Ara ja podem tenir tot el codi BIML amb colorets i amb els indicadors d'errors correctes. També té un preview de codi que és molt útil quan tenim bucles o variables que s'agafen de BBDD, així podem veure com quedarà el codi BIML final que ens generarà els DTSX.
Per poder instal·lar el BIML Express 2018 cal la versió 4.7 del framework.net.

Les notes de la versió són: https://varigence.com/Documentation/BimlExpress/Article/BimlExpress+2018
Aquí teniu el link per descarregar-lo: https://varigence.com/downloads/bimlexpress.vsix

Timeouts a la SSISDB

Un dels problemes que m'he trobat quan executo paquets de SSIS que estan guardats en la SSISDB es que si s'executen molts a la vegada a vegades tenim error de timeout a l'hora d'accedir al paquet.
Les dues taules principals que es consulten a l'hora d'iniciar l'execució d'un paquet a través d'un job són:
- internal.object_parameters: conté els paràmetres de cada paquet
- internal.execution_parameter_values: conté els paràmetres que s'han utilitzat en cadascuna de les execucions

El que ens interessa es tenir aquestes dues taules el més petites possible.
Per tal de reduir internal.object_parameters el que hem de fer és que el "Maximum Number of Versions per Project" sigui el més petit possible. En cas que tinguem algun sistema de versionat de codi ho podrem deixar a algun valor bastant baix com 3. En cas que no tinguem cap repositori de codi el risc de rebaixar-ho és molt més alt.
Per reduir internal.execution_parameter_values el que hem de fer és que el "Retention Period (days)" sigui el més petit possible. Aquí depèn de la quantitat de logs que vulguem tenir, però si volem tenir històric es poden bolcar les dades a un Datawarehouse i tenir els logs consultables a la SSISDB de 10 dies.

Si encara seguim tenint timeouts podem modificar el procediment: catalog.create_execution
A la línia 168 tenim el nombre d'intents per accedir al paquet. Per defecte està a 2, podem augmentar el valor per que faci més intents.
A la línia 174 hi ha la variable @LockTimeOut que són els segons que s'espera per tornar a fer un nou reintent.

DECLARE @lock_result int
DECLARE @retry int
SET @lock_result = -1
SET @retry = 2
WHILE @retry > 0 AND @lock_result < 0
BEGIN
EXEC @lock_result = sp_getapplock
        @Resource = 'MS_ISServer_Create_Execution',
        @LockTimeOut= 5000,
        @LockMode = 'Exclusive'

    SET @retry = @retry - 1
END


Si havent reduit les versions, el log i modificant el procediment encara tenim timeouts només ens queda l'opció de reduir els paquets que s'executen en paral·lel o posar més ferro al servidor.

DLLs externes SQL Server (2016)

Fins a la versió d'SQL Server 2014 si voliem utilitzar DLL externes en teníem prou en copiar-les a C:\Program Files (x86)\Microsoft SQL Server\<versió sql server>\DTS\Binn o C:\Program Files\Microsoft SQL Server\<versió sql server>\DTS\Binn segons si la DLL es de 30 o 64 bits.
A partir de la versió 2016 hem de fer les coses ben fetes i cal registrar les DLL amb la utilitat gacutil.

Gacutil el podem trobar a dins de C:\Program Files (x86)\Microsoft SDKs\Windows depenent de la versió del framework .net que tinguem instal·lat.
Per exemple "C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil"

Aquest és un exemple de comanda per utilitzar la DLL d'AWS. On a c:\dll teim les dll que volem registar.

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil" /i C:\dlls\AWSSDK.Core.dll

Un cop registada la DLL apareixerà a la següent carpeta:
C:\Windows\assembly

La referència a utilitzar en els scripts del SSDT serà del tipus:
C:\Windows\assembly\GAC_MSIL\AWSSDK.Core\3.3.0.0__885c28607f98e604\AWSSDK.Core.dll

Amb aquestes comandes ja podrem utilitzar DLLs externes a dins dels nostres Script Task a partir en SQL Server.

Emmascarar noms de taules i camps MDS

En el passat post veiem com migrar models entre entorns o entre versions. Un dany col·lateral de la migració és que els noms de les taules i dels camps que genera automàticament l'MDS. Això ens impacta a l'hora de fer ETLs o altres processos que utilitzin les taules d'MDS. Una solució es crear una vista que emascari aquests noms variables, però aquest procés ha de ser el més automàtic possible per evitar accidents humans.
Avui veurem un procediment per poder crear automàticament les vistes. Aquest procediment utilitzarem les sentències que vam veure en el post: http://www.eljordifabi.tech/2018/03/crear-una-vista-en-una-altra-bbdd.html

Migració MDS I (movent models entre servidors)

Quan treballem en diversos entorns, o bé hem de fer una migració de versió de MDS, per moure/migrar els models no ho podem fer copiant directament les dades d'una BBDD a una altra. Si és la mateixa versió per que no podem garantir que els ids siguin els mateixos (pot haver agents externs que hagin afegit models, per exemple) i en una migració per que les taules no són les mateixes ni tenen la mateixa esructura.
Per tal de fer el moviment de models entre servidors utilitzarem la utilitat MDSModelDeploy.

MDSModelDeploy està a la carpeta c:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration
En el cas de tenir el servidor en SQL Server 2014, si és SQL Server 2016 la carpeta és la 130.

El primer que hem de fer és trobar el nostre servei fent un listservices.

En el nostre cas és el MDS5 (MDSPRO).

Crear una vista en una altra BBDD

En algun cas ens podem trobar amb la necessitat de crear una vista en una BBDD diferent de la que estem, per exemple dins d'un store procedure, però ens trobem amb 2 problemes:

• La sentència create view ha de ser la 1a, amb el que no podem fer servir un:

 use <bbdd> create view ...

• No podem especificar la BBDD en la sentència create

create view <bbdd>.dbo.view as

En aquests casos podem utilitzar el procedure sp_executesql posant-li a dins la sentència create.
Aquest store procedure s'invocarà des de la BBDD a la cual volem crear la vista.

exec <bbdd>.sys.sp_executesql  'create view v_xxxx as ...'

Aquesta sentència ens ajudarà a crear vistes per a estar preparats per a una migració poc dolorosa d'MDS, que ho veurem al proper post.