3 Replies Latest reply on Aug 7, 2017 6:54 AM by AlasdairRobertson

    Create a SQL data connection on HSM

    alouhichi2 Apprentice

      Hi everyone,


      I am newbie on HSM and have a question please related to the data import based on a Database Sql Connection.


      I have to import into HSM data provided by A SQL Server View from a third application provider


      When I check documentation and have a look on different types of connection provided on HSM (2016.2) I don’t found a manner to connect to a database (Sql or other)


      Is this correct or have me missing something please ?


      If it is the case, how customer are doing to import and populate the HSM (like CMDB project or other referential sources) . Doing this by importing files will be a hard job and not really synchronized I suppose. 


      Many thanks



        • 1. Re: Create a SQL data connection on HSM
          AlasdairRobertson ITSMMVPGroup

          There is no database connector for SQL, yes that is a pain, feel free to raise a feature request or an idea for it. 


          Workaround: if it is SCCM or Discovery then there are native connectors for everything else you need to either use an API integration or if it is SQL Server we usually write SSIS (SQL Server Integration Package) packages to extract the information to a CSV file or xml and then import for a shared folder (or FTP/SFTP if SaaS).  The SSIS export can be scheduled and the import to ISM scheduled to a frequency that suits (hourly for example depending on the data refresh frequency)


          I hope that helps, if you get stuck just ask here and someone will help out.

          • 2. Re: Create a SQL data connection on HSM
            alouhichi2 Apprentice



            Thank you for replying this.

            I agree that using SSIS package can be a workaround but from my experience, not all customers accept this method and found it heavy to maintain

            Each time they need to add an attribute, they have to edit SQL view and edit SSIS package and export it again

            Also they have to manage concurrent access (and security aspects) for CSV files when exported files are used by more than one application (If an application use the file, this will be locked and interrupt the other import, so they have to schedule each one or export in different folder destination) 


            Have you an idea how SaaS customer manage this case ? I suppose that API is the best solution because using a shared folder seems to be complicated.


            Thank you  very much



            • 3. Re: Create a SQL data connection on HSM
              AlasdairRobertson ITSMMVPGroup

              SaaS customers use manual uploads, ftp/sftp sites and API connections.  Depends on the resources the customer has and if the upload if a one off or not.