Hi Sergio,
Below is the code:
import streamlit as st
import pandas as pd
import sqlalchemy
import urllib.parse
general_folder = âC:\\Web Appsâ
config_file_name = âClient Data Processing Configuration.xlsxâ
config_file_location = general_folder + â\â + config_file_name
Load config file into the dataframe
xls = pd.ExcelFile(config_file_location)
config_df = pd.read_excel(xls, âJobsâ)
st.title(âSelf Serve Data Processingâ)
Identify a client
client_list = list(set(config_df[âClient Nameâ]))
client = st.selectbox(âClientâ, client_list)
Identify the client job to run
config_df_client = config_df[config_df[âClient Nameâ] == client]
data_processing_jobs_for_client = list(config_df_client[âData Processing Jobâ])
data_processing_job = st.selectbox(âData processing Jobâ, data_processing_jobs_for_client)
Identify client and etl database server and database
client_db_server = list(config_df_client[âDatabase Serverâ])[0]
client_database = list(config_df_client[âDatabase Nameâ])[0]
etl_db_server = list(config_df_client[âETL Serverâ])[0]
etl_database = âmsdbâ
def get_connection(db_server, database):
# Database connection
connection_string = (
r'Driver={SQL Server Native Client 11.0};'
r'Server=' + db_server + ';'
r'Database=' + database + ';'
r'Trusted_Connection=yes;'
)
params = urllib.parse.quote(connection_string)
engine = sqlalchemy.create_engine(
"mssql+pyodbc:///?odbc_connect=%s" % params
)
connection = engine.raw_connection()
return connection
client_connection = get_connection(client_db_server, client_database)
etl_connection = get_connection(etl_db_server, etl_database)
if st.button(âStart Jobâ):
etl_connection.execute(
"SET NOCOUNT ON; EXEC sp_start_job N'"+data_processing_job
+ "'; SET NOCOUNT OFF"
)
st.success("The job has started successfully")
if st.button(âStop Jobâ):
etl_connection.execute(
"SET NOCOUNT ON; EXEC sp_stop_job N'"+data_processing_job
+ "'; SET NOCOUNT OFF"
)
st.success("The job has stopped successfully")
if st.button(âView Job Progressâ):
sql = "SELECT * FROM [audit].[PackageStatusForLatestBatch] ORDER BY [Package Start Time]"
audit_view = pd.read_sql_query(sql, client_connection)
st.dataframe(audit_view)
Things to note:
-
PackageStatusForLatestBatch is a view built on top of the audit tables which shows SSIS package run progress. Those audit tables are populated by the SSIS packages. If youâd like to know how that works, it is a separate discussion on SSIS Management Framework. Alternatively, you can replace with your own way of identifying/showing progress.
-
There is a configuration excel file (âClient Data Processing Configuration.xlsxâ), which contains information on clients and jobs per client as well as database server information. This excel file has 1 tab (Jobs), and within this tab the columns are:
Hope this helps.
Any questions, let me know.
Thanks!