locked
SQL Server 2016 SSISDB new stored procedure RRS feed

  • Question

  • Hi, We are using SQL Server and SSIS 2016. I am looking for an example using the stored procedure [catalog].[deploy_packages] to deploy one or more packages to SSISDB.

    Thank you

    SQLEnthusiast

    Tuesday, June 13, 2017 6:56 PM

All replies

  • Hi SQLEnthusiast,

    To use the [catalog].[deploy_packages], you can refer to following example:

    private static void Main(string[] args)  
    {  
        // Connection string to SSISDB  
        var connectionString = "Data Source=.;Initial Catalog=SSISDB;Integrated Security=True;MultipleActiveResultSets=false";  
    
        using (var sqlConnection = new SqlConnection(connectionString))  
        {  
            sqlConnection.Open();  
    
            var sqlCommand = new SqlCommand  
            {  
                Connection = sqlConnection,  
                CommandType = CommandType.StoredProcedure,  
                CommandText = "[catalog].[deploy_packages]"  
            };  
    
            var packageData = Encoding.UTF8.GetBytes(File.ReadAllText(@"C:\Test\Package.dtsx"));  
    
            // DataTable: name is the package name without extension and package_data is byte array of package.  
            var packageTable = new DataTable();  
            packageTable.Columns.Add("name", typeof(string));  
            packageTable.Columns.Add("package_data", typeof(byte[]));  
            packageTable.Rows.Add("Package", packageData);  
    
            // Set the destination project and folder which is named Folder and Project.  
            sqlCommand.Parameters.Add(new SqlParameter("@folder_name", SqlDbType.NVarChar, ParameterDirection.Input, "Folder", -1));  
            sqlCommand.Parameters.Add(new SqlParameter("@project_name", SqlDbType.NVarChar, ParameterDirection.Input, "Project", -1));  
            sqlCommand.Parameters.Add(new SqlParameter("@packages_table", SqlDbType.Structured, ParameterDirection.Input, packageTable, -1));  
    
            var result = sqlCommand.Parameters.Add("RetVal", SqlDbType.Int);  
            result.Direction = ParameterDirection.ReturnValue;  
    
            sqlCommand.ExecuteNonQuery();  
        }  
    }  
    

    For more details, please refer: Deploy packages by using the deploy_packages stored procedure 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Thursday, June 22, 2017 6:45 AM
    Wednesday, June 14, 2017 1:46 AM