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