What is the procedure to open a ticket for Microsoft??

Here is the information social.msdn.microsoft.com

In the above link as of today (12-20-2016) we had following link:
https://support.microsoft.com/oas/default.aspx?gprid=1044&st=1&wfxredirect=1&sd=gn

Advertisements

Various approaches for SSIS upgrade or migration from 2012 to 2016

As per my understanding there can be 3 approaches for SSIS upgrade or migration from 2012 to 2016.

We created a new instance of SSIS 2016 (SQL Server 2016 – SSIS catalog db).

  1. First approach is using SSDT open the package and it will get converted and then deploy to the 2016 server.
  2. Take the backup of 2012 package and restore the ISPAC package on 2016 server.
  3. In above 2 approaches we will have to do individually each package. So this third approach.
    1. On the 2016 server create SSIS catalog.
    2. Restore the SSISDB from 2012 to 2016 server.
    3. Run the SSIS Upgrade wizard.
    4. Update the Master Key using:

USE [SSISDB]
OPEN master Key decryption by password = ‘Password’ — when creating 2012 SSISDB
ALTER Master Key ADD encryption by Service Master Key

Update the connection strings and password.

Script to update server name in SSIS package

We are doing SQL server migration from 2012 to 2016. One of the challenges was how to update server name in SSIS package programatically without the manual process of opening each package in SSMS =>. configure => connection strings and change them manually.

Use following update SQL scripts:

update [SSISDBName].[internal].[object_parameters]
set design_default_value = ‘NewServername’
where design_default_value in (‘OldServerName1′,’OldServerName2’)
update [SSISDBName].[internal].[object_parameters]
set default_value = ‘NewServername’
where default_value in (‘OldServerName1′,’OldServerName2’)

SSDT 2012 IDE takes forever to load SSIS project giving message validating OLE DB source

SSDT (Former BIDS) 2012 IDE takes forever to load SSIS project giving message validating OLE DB source.

The James Serra blog and comments explains the problem and solution

The three options around this problem: The DelayValidation property, the ValidateExternalMetadata property, and the “Work Offine” option.

As mentioned in the comments section, I was unable to open the project.
So using task manager, killed the process.
Edited the .dtproj.user file. Changed the “OfflineMode” from false to true and then open the project.

oracle to sql server data type mapping for ssis package using oracle attunity

We are creating an SSIS 2012 package to take data from Oracle in to SQL server using Oracle Attunity driver.

We used intermediate table approach.
We created an intermediate table on SQL server side and brought all the data from Oracle to SQL server and then moved the data into final SQL server.
The timestamp field on Oracle was mapped to varchar (75) on SQL server intermediate table side.

The date field on Oracle was mapped to datetime and not datatime2 on SQL server intermediate table side. By default the software tries to use datetime2, which we were unable to make it working.

 

How to do IF condition in SSIS package

Just followed this awesome blog to do this

Created a variable in package scope name it say UploadEnableFlag.

Created an Execute SQL task.

Configured this task to return Select count(*) as UploadEnableFlag from ControlTable.

Change the result set to Single row. Now you can add the Result Sets.

In Result Set tab, add a result set with name say UploadEnableFlag and map it with variable User::UploadEnableFlag.

Connect green line from this Execute Process Task to the task ( true condition).

Right click on this green line (called Precedence Constraint) and select Edit.

In Precedence Constraint Editor:

  • set evaluation operation as Expression
  • in Expression box write : @[User::UploadEnableFlag]>0

Connect another green line from this Execute Process Task to the task ( false condition).

In Precedence Constraint Editor:

  • set evaluation operation as Expression
  • in Expression box write : @[User::UploadEnableFlag]<=0

The package is ready for testing.