Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This SQL query returns all the SSIS packages that are visible if the "Integration Services Catalogs" node in SQL Server Management Studio is expanded:
SQL
USE SSISDB

SELECT [package_id]
	  ,fd.name as fd_name
	  ,proj.name as proj_name
      ,pk.[name] as pk_name
      ,[package_guid]
FROM [catalog].[packages] pk
	INNER JOIN [catalog].[projects] proj ON pk.project_id = proj.project_id
	INNER JOIN [catalog].[folders] fd ON proj.folder_id = fd.folder_id
ORDER BY fd.name, proj.name, pk.name


What I have tried:

In C#, I am using the code below to iterate through the above data table.
("cn" is a class with the string attribute "Connstr" and the method "DataTableSQL" which returns the DataTable object and accepts the above SQL string as an argument).
None of the packages are encrypted or require a password to execute, i.e. "null" arguments for "serverUserName" and "serverPassword" are used.
Connection string:
"Data Source=Server name;Initial Catalog=Database name;Integrated Security=True;Persist Security Info=False"
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(cn.ConnStr);
Application ssisApplication = new Application();

DataTable dt = cn.DataTableSQL(<SQL>);
foreach (DataRow dr in dt.Rows)
{
    string fd_name = "", proj_name = "", pk_name = "", pk_location = "";
    if (!dr.IsNull(1)) fd_name = dr[1].ToString();
    if (!dr.IsNull(2)) proj_name = dr[2].ToString();
    if (!dr.IsNull(3))
    {
        FileInfo fp = new FileInfo(dr[3].ToString());
        pk_name = fp.Name.Substring(0, fp.Name.Length - fp.Extension.Length);
        pk_location = string.Format(@"\{0}\{1}\{2}", fd_name, proj_name, pk_name);
    }

    try
    {
        Package pkg = new Package();
        pkg = ssisApplication.LoadFromSqlServer(pk_name, csb.DataSource, null, null, null);
        // Processing steps to be added to parse pkg.Executables...
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.Message, pk_name);
    }
}

I get this error for every data row:
Quote:
The specified package could not be loaded from the SQL Server database.

Which combination of "fd_name", "proj_name" and "pk_name" would it be best to use for "pk_location" if this is used as the first argument of the "LoadFromSqlServer" function instead of "pk_name"?
Any help would be much appreciated.
Posted
Comments
RedDk 15-Mar-19 14:36pm    
"Which combination of .. etc"? Obviously, any one which works. "Best" ... not a word that has any meaning in programming sense especially when not being used as a keyword in the code.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900