Microsoft SQL Server integration services Script Task ' write scripts using Microsoft visual Basic ' The ScriptMain class is the entry point of the Script Task.

imports System Imports System.Data

imports System.Data.SqlClient

                                                                                                               

Imports System.Math

imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain

 

Public Sub Main()

'create a new SqlConnection object with the appropriate connection string Dim sqlconnstring as String

Dim sqlConn as New SqlConnection '(connectionstring) 'OleDbConnection i

Dim sqlConnDB20 as New SqlConnection '(connectionstring) 'OleDbConnection i

Dim sqlconnsp as New SqlConnection '(connectionstring) 'OleDbConnection i

' create the command object

Dim sqlComm as New SqlCommand

Dim sqlCommDB20 as New SqlCommand

Dim sqlCommSP As New SqlCommand

Dim r As SqlDataReader

Dim rsp as SqlDataReader

Dim SQL As String

Dim Server as String

Dim Database As String

Dim SRecID As Integer

Dim DBPassword As String

Dim ServerType as String

 

Try

'open a connection to Sharedb20

sqlconnstring = "Server=iT-DeV-SQL;Database=dBUti1ities;Trusted_Connection=True;" sqlConnDB20.Connectionstring = sqlconnstring sqlConnDB20.Open()

'Create the Temp table to hold the results

SQL = "create table ##ServerDBStatus (SQLServer varchar(lOO), DBName varchar(100),Status

varchar(100),TheMode varchar(50),Dateofstatus datetime,Message_Text varchar(max) )"

sqlCommDB20.CommandText = SQL sqlCommDB20.Connection = sqlConnDB20 sqlCommDB20. ExecuteNonQuery()

 

'now connect to Devdb03 to get the list of server and database.

sqlconnstring = "server=iT-DEV-SQL;Database=dbuti1ities;Trusted_Connection=True;" sqlConn.connectionstring = sqlconnstring ' open the connection sqlConn.open()

sqlComm.CommandText = "select * from dbutilities.dbo.tblserverLookup Where online_status_scan 1 and   Active = 1 "

'sqlComm.ExecuteReader() sqlComm.connection = sqlconn

r = sqlComm.ExecuteReader while r.Read()

server = cstr(r("serverName"))

Database = CStr(r("Loc_Admin_SP"))

ServerType = CStr(r("ServerType"))

'now set the Passwords   note: this will need be changed once a Year Select Case ServerType Case "Prod"

DBPassword = "F2dENTq0Nselo!+" Case "Test"

DBPassword = "uusuu$dkiwGE0$9" Case "Dev"

DBPassword = "14wixiD!euuc2n#()" End Select

'now Loop through the Servers and execute the Failed Job sp 'sqlConnString = "Server=" & Server & ";Database=" & Database & "; trusted_connection=true;"

sqlconnstring = "server=" & server & ";Database=" & Database & ";user

 

iD=DBA_Job;Password=" & DBPassword & ""

'Provider=sqloledb;Data Source=myServerAddress;initial Catalog=myDataBase;user Id=myusername;Password=myPassword;

"MsgBox(sqlConnString)

sqlConnSP.Connectionstring = sqlconnstring Try

sqlConnsP.Open() Catch exception as system.Exception 'Record the error First

SQL = "insert into ##ServerDBStatus (SQLServer,Status,DateofStatus,Message_Text)" _
& " values ('" & CStr(server) & "', 'would Not allow
a connection','" &
CStr(Now) & "','"  & QuotesFix
(cstr(exception.Message.Tostring)) & "')"

sqlCommDB20.CommandText = SQL sql CommDB20. ExecuteNonQuery()

'if there is a login failure then Look to see if it due to not having the DBA_job account if so then add it

Try

sqlconnstring = "server=" & server & ";Database=" & Database & ";Trusted_Connection=True;"

sqlConnSP.Connectionstring = sqlconnstring sqlConnSP.Open()

SQL = "exec sp_addlogin 'DBA_job', '" & DBPassword & "' " sqlCommSP.Connection = sqlConnSP sqlCommSP.CommandText = SQL sqlCommSP. ExecuteNonQuery()

SQL = "exec sp_addsrvrolemember 'DBA_Job', 'sysadmin';" sqlCommSP.Connection = sqlConnSP sqlCommSP.CommandText = SQL sqlCommSP.ExecuteNonQuery() sqlConnSP.close() Catch ex as Exception 'Record the error

SQL = "insert into ##ServerDBStatus (SQLServer,Status,DateofStatus,Message_Text)" _

& " values ('" & CStr(server) & "','Had Trouble Adding the New Login for
DBA_job','" & cstr(Now) & "', '" & QuotesFix(cstr(exception.Message.Tostring)) & '")"

sqlCommdB20.CommandText = SQL sqlCommDB20. ExecuteNonQuery() End Try

 

End Try

If sqlConnSP.State = Connectionstate.open Then sqlCommSP.Connection = sqlConnSP

sqlCommSP.CommandText = "Execute Database_Online_Status " Try

rsp = sqlcommSP.ExecuteReader

 

Catch ex as Exception

'if failed Here then Most likly it did not have SP

'So Go and create it on this server so next time this runs it will not fail at

this point.

SQL = "Create procedure Database_Online_Status" & vbcrLf SQL = SQL & " as " & vbcrLf

SQL = SQL & " " & VbcrLf

SQL = SQL & " declare @OBName varchar(lOO)" & vbcrLf

SQL = SQL & " DECLARE @SQLString NVARCHAR(500)" & vbCrLf

SQL = SQL & " Declare @TotalCount as int" & vbcrLf SQL = SQL & " Declare @TheCount as int" & vbcrLf SQL = SQL & " " & vbcrLf

SQL = SQL & " " & VbCrLf SQL = SQL & " " & VbCrLf

SQL = SQL & " create table ##DatabaseStatus " & vbcrLf

SQL = SQL & "   (RecID [int]  IDENTITY(1,1) NOT NULL," & vbCrLf

SQL = SQL & " DBName varchar(lOO) primary key, " & vbcrLf

SQL = SQL & " status varchar(lOO), " & vbcrLf

SQL = SQL & " Mode varchar(lOO) )" & vbcrLf

SQL = SQL & " " & vbcrLf

SQL = SQL & " set nocount on" & vbcrLf

SQL = SQL & " " & VbCrLf

sql = sql & " -- will loop through all databases in the instance." & vbcrLf sql = sql & " insert into ##DatabaseStatus (DBName)" & vbcrLf sql = sql & " select name from master..sysdatabases" & vbcrLf sql = sql & " order by name" & vbcrLf

SQL = SQL & "  " & VbCrLf

SQL = SQL & " --set the counts to get started" & vbcrLf SQL = SQL & " " & vbcrLf

 

SQL = SQL & " set @TotalCount = (select Count(*) from ##DatabaseStatus)" & vbcrLf

SQL = SQL & " set @Thecount = 1 " & vbcrLf

SQL = SQL & " " & vbcrLf

SQL = SQL & " -- now loop Through Temp table and get the status each db on this

server" & vbcrLf

sql = sql & " while @Thecount <= @TotalCount" & vbcrLf

SQL = SQL & " begin " & vbcrLf

sql = sql & " -- Set the DBName" & vbcrLf

SQL = SQL & "  set @DBName = (select DBName from ##DatabaseStatus where ReciD =

@TheCount)" & vbcrLf

SQL = SQL & " -- update Server status and Access Type" & vbcrLf

SQL = SQL & "  update ##DatabaseStatus " & vbcrLf

SQL = SQL & "  set Status = (select convert(nvarchar(100), databasepropertyex (
@DBName ,'Status'   )))," & vbcrLf

SQL = SQL & "  Mode =   (select convert(nvarchar(100),databasepropertyex ( ©DBName
,'userAccess'   )))" & vbcrLf

sql = sql & "  where ReciD = @TheCount" & vbcrLf

SQL = SQL & "  -- up the counter " & vbcrLf

SQL = SQL & "  set @TheCount = @Thecount + 1" & vbcrLf

SQL = SQL & "  End" & vbcrLf

SQL = SQL & "  " & VbCrLf

SQL = SQL & " set nocount on" & vbcrLf

SQL = SQL & "  " & VbCrLf

SQL = SQL & " Select * from ##DatabaseStatus " & vbcrLf

SQL = SQL & "  " & VbCrLf

SQL = SQL & " drop table ##DatabaseStatus"

sqlCommSP.Connection = sqlConnSP sqlCommSP.CommandText = SQL Try

sql CommsP. ExecuteNonQuery() 'Record the Action for the report SQL = "insert into ##ServerDBStatus (SQLServer,status,Dateofstatus,Message_Text)" _

& " values ('" & CStr(Server) & "','Added the Store Procedure for
Next Run','" & cstr(Now) & "','"   & QuotesFix(
cstr(ex.Message.Tostring)) & "')"

sqlCommDB20.CommandText = SQL sqlCommDB20. ExecuteNonQuery() sqlConnSP.Close() Catch exc As Exception

SQL = "insert into ##ServerDBStatus (SQLServer,status,DateofStatus,Message_Text)" _

& " values ('" & CStr(Server) & "','Added the Store Procedure for Next
Run','" & cstr
(Now) & "','"   & QuotesFix(cstr(exc.Message.Tostring)) & "')"

sqlCommDB20.CommandText = SQL sqlCommDB20. ExecuteNonQuery() sqlConnSP.Close() End Try End Try Try

while rsp.Read

'now loop through and add the temp table if any jobs have failed. SQL = "insert into ##ServerDBStatus (SQLSever., dbname, Status, TheMode, DateofStatus) " _

& " values ('" & cstr(server) & "','"   & QuotesFix(CStr(rsp("DBName")))

& "','" & QuotesFix(CStr(rsp("Status"))) & "','"    & cstr(rsp("Mode")) & "',"' & cstr(Now) & '")"

sqlCommDB20.CommandText = SQL sql CommDB20. ExecuteNonQuery() End While rsp.close() sqlConnSP.Close() Catch ex as Exception 'Record the error

SQL = "insert into ##serverDBStatus (SQLServer,status,Dateofstatus,Message_Text)" _

& " values ('" & CStr(Server) & "','Had Trouble Reading from SP','" &
CStr
(Now) &  "','"        & QuotesFix(cstr(ex.Message.Tostring)) & "')"

sqlCommDB20.CommandText = SQL sqlCommDB20. ExecuteNonQuery() rsp.close() sqlConnSP.Close() End Try

End If End while

r.close()

'now Set out the Report if there is anything on it.

'to do this we are going to execute a SP that create the report


untitled

SQL = "Execute [Send_Database_Status_Report]"

sqlCommDB20.CommandText = SQL

sqlCommDB20. ExecuteNonQuery()

sqlConn.Close()

sqlConnDB20.Close()

Dts.TaskResult = Dts.Results.Success Catch exception As System.Exception

Dts.TaskResult = Dts.Results.Failure End Try

End Sub

Private Function QuotesFix(Byval ntext as String) as String if instr(l, ntext,    "'"   ) <> 0 Then

QuotesFix = Replace(ntext, "'", "'")

Else

QuotesFix = ntext End If End Function

End Class