How to loop through multiple Sql Server instance, database, and table/view names?

Question

It's been a long time since I write any C# code. I'm trying to query some tables and views from different SQL Server instance, database names, and tables/views. Below is my sample code.

public void Main()
{
            // TODO: Add your code here
            string connectionString = @"Server = db1; Database = dbName; Trusted_Connection = True; ";
            //string DatabaseName = "db1";
            //string TableName = "tbl1";
            string Delimiter = ",";
            
            SqlConnection sqlCon = new SqlConnection(connectionString);
            sqlCon.Open();
            SqlCommand sqlCmd = new SqlCommand(@"select DatabaseName, TableName from [dbo].[Config_Table] where active =1", sqlCon);
            SqlDataReader reader = sqlCmd.ExecuteReader();
            while (reader.Read())
            {
                DatabaseName = reader.GetString(0);
                TableName = reader.GetString(1);
                ExportData(DatabaseName, TableName, Delimiter, connectionString);
            }
            reader.Close();
            sqlCon.Close();
            Dts.TaskResult = (int)ScriptResults.Success;
}

I want to write a while or foreach loop to go through the number of databases and tables/tables and then in the ExportData function, export to .csv file based on the table/view name. Eventually this script will be part of my SSIS package.

I'm resorting to script because it seems like the drag-and-drop GUI of the integration services inside of Visual Studio does not have a simple way to looping different sql server instance, database names, tables and views.

Any help is much appreciated!

Answer 1

It is unclear to me but are you trying to enumerate the SQL databases and tables contained in a specific SQL instance or are you trying to query data from your custom table that happens to contain database/table information about SQL servers?

If you want to get the actual databases/tables from a SQL instance then that is what the INFORMATION_SCHEMA is for.

Note that using a data reader is probably not worth your effort here given the small subset of data you're working with. Also note that you really shouldn't be using raw connection here. SSIS has a connection manager that manages this so you should create a connection manager to the DB you want to connect to and then use DTS to get the connection from there in your script.

private IEnumerable<string> GetDatabases ( SqlConnection conn )
{
   var ds = new DataSet();
   var da = new SqlDataAdapter("SELECT Name from sys.databases", conn);
   da.Fill(ds);
   
   return ds.Tables[0].Rows.OfType<DataRow>().Select(x => x["name"];
}

private IEnumerable<string> GetTables ( SqlConnection conn, string database )
{
   conn.ChangeDatabase(database);

   var ds = new DataSet();
   var da = new SqlDataAdapter("SELECT Table_Name from INFORMATION_SCHEMA.TABLES", conn);
   da.Fill(ds);
   
   return ds.Tables[0].Rows.OfType<DataRow>().Select(x => x["Table_Name"];
}

//TODO: You should be getting this information using a ConnectionManager provided by DTS and not hard coded in your script
string connectionString = @"Server = db1; Database = dbName; Trusted_Connection = True; ";

//TODO: DTS connection managers already expose a connection to you so this code goes away
//but for demo purposes...
using (var conn = new SqlConnection(connectionString))
{
   conn.Open();

   var databases = GetDatabases(conn);

   foreach (var database in databases)
   {
      var tables = GetTables(conn, database);
      foreach (var table in tables)   
      {
         ExportData(database, table, Delimiter, connectionString);
      };
   };
}

Alternatively, and potentially better, is to use the SMO library that can look up all this information without you have to write SQL queries yourself. I'm not sure if it works in SSIS scripts or not though, I assume it does.

Note: Not tested.

var server = new Server(connectionString);
foreach (Database database in server.Databases)
{
   foreach (var table in database.Tables)   
   {
      ExportData(database.Name, table.Name, Delimiter, connectionString);
   };
};