SSIS can be leveraged in HDInsights projects as effectively as in other BI projects. Once the data resides on Hadoop clusters, you surely want to import data to Cube or DW for further analytics. There are multiple ways to achieve the desired purpose:
1. Using SQOOP connector:
SQOOP connector basically fires a command on Hadoop Command prompt and imports the data from HDFS/ Hive to your SQL Server tables. To automate the process, you can wrap the SQOOP command in a batch file and execute this batch file from SSIS.
2. Using Hive data source in SSIS:
For this, one has to create a Hive data source and then in SSIS, use this data source to dump data to destination.
Note: 2nd option is not available in preview versions of HDInsights.