Downgrading SSIS package from 2014 to 2012.

The Workaround (Step by Step)

1. If you haven’t done so, open DTSX Version 2012/01 schema
2. Open the SSDT-BI VS 2013 DTSX package in a text editor
3. Search for PackageFormatVersion. Change the value from 8 to 6
4. Replace all instances of Microsoft.Package with SSIS.Package.3
5. Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
6. Search for all instances of DTS:ExecutableType=”
   
7. For each ExecutableType value (Microsoft.ExecuteSQLTask, etc.), go to theDTSX Version 2012/01 schema and search under

   <xs:simpleType name="AnyNonPackageExecutableTypeExecutableTypeEnum">

   for the correct ExecutableType. For example, Microsoft.ExecuteSQLTask in SSIS 2014 should be Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9 in SSIS 2012.
8. Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
9. Search for all instances of componentClassID=”Microsoft
   
10. For each componentClassID value (Microsoft.DerivedColumn, Microsoft.Lookup, etc.), go to the DTSX Version 2012/01 schema and search under

    <xs:simpleType name="PipelineComponentComponentClassIDEnum">

    for the corresponding GUID.
11. Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
12. Now open the package in Visual Studio 2012. If your package does not have upgraded components, then it should open by now. Otherwise, read further.

The Exception

Some components, such as the Script Component in the Data Flow Task, have been upgraded from SSDT-BI 2012 to SSDT-BI 2014. In this case, it’s not only the property / attribute value that is different, but also the structure of the XML node. In this case, replace the Version 2014/01 schema value (e.g. componentClassID=”Microsoft.ManagedComponentHost”)  with any valid DTSX Version 2012/01 componentClassID. Then open the package in VS 2012. The component will be marked as with error, that’s OK. If you have followed all the steps above, your package should at least load the designer by now. Now remove this erroneous component and re-do it in VS 2012 itself.

Granting a access to SSIS server

To grant access to the Integration Services service

1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
           a) On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
   
          b) In the SQL Server Configuration Manager snap-in, locate SQL Server Integration Services in the list of services, right-click SQL Server Integration Services, and then click Restart. 
9. Account might require sysdba role, if the following error shows up.
   "The Execute permission was denied on the object 'sp_ssis_listfolders'.

List all the jobs with ssis package

USE [msdb]
GO
SELECT
   Srv.srvname AS ServerName,
   Job.name AS JobName,
   JStep.step_id,
   JStep.step_name AS StepName,
   JStep.command,
   Job.enabled
FROM   dbo.sysjobs Job
JOIN   dbo.sysjobsteps JStep
   ON  JStep.job_id = Job.job_id
JOIN   MASTER.dbo.sysservers Srv
   ON  Srv.srvid = Job.originating_server_id
WHERE  JStep.subsystem='SSIS' AND enabled=1

ORDER BY Job.name,step_id

SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.