• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

how to run .sql files in SSIS package and if any occur after a script then need to be alert and package should stop and output messages need to be written to flat file

hi,
i have lot scripts in my environment need to be run on weekly.
my TL asked me to do automation running scripts.
the output messages should write to a file and if any errors in messages then need to stop running next script and need to send mail to TL.
i have 3 option 1) assigning jobs but my TL dont want that
2)sqlcmd but i dont know how to stop if errors found in output messages adn how to send message
3)SSIS package iam not able to send output messages to flat file

please help me
thanks in advance

Brahma

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO
 
INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\SQL Scripts\build 8.1.24\*.sql"'
GO
 
DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]
select * from ##SQLFiles
--drop table ##SQLFiles
DECLARE @vFileName            VARCHAR(200)
DECLARE @vSQLStmt             nVARCHAR(4000)
 
OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
print @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- The following SET command must be on a single line or else an error will be generated.
    -- It is split in this script for readability purposes.
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''sqlcmd -S srivenkateswara\sql2k5 -i"C:\SQL Scripts\build 8.1.24\' +  @vFilename  + '" -o  "C:\SQL Scripts\' + substring(@vFileName,1,len(@vFileName)-4) + '.txt'+'"'''
exec    sp_executesql @vSQLStmt
if @@error >0
break;
	print @vSQLStmt
 
    FETCH NEXT FROM cFiles INTO @vFileName
END
 
CLOSE cFiles
DEALLOCATE cFiles
GO
 
DROP TABLE ##SQLFiles
GO
 
--this is the sqlcmd script

Open in new window

0
brahma007
Asked:
brahma007
  • 15
  • 15
  • 4
  • +1
6 Solutions
 
HoggZillaCommented:
Use SSIS, you can schedule it, log it, send emails and write output files. You mentioned that in SSIS you could not send output messages to flat file - use a script task.
0
 
PedroCGDCommented:
You can do that in SSIS.

3)SSIS package iam not able to send output messages to flat file
You can also do that...

Tell me your problems using SSIS... and attach here the database scripts you think is necessary.
Regards!
Pedro
0
 
brahma007Author Commented:
hi PerdoCGO,

Let me explain what i did
i used foreach loop,  Execute SQL task and script task in my package.
foreachloop working fine and even execute task also
but i am getting problem how to send output messages anything like print statements and rows updated messages to flatfile using script task. i am using resultset ="full result set"
and if any script fails how i can send mail
i dont have any script with me plz give me idea how to make things working
Public Sub Main()
        Dim outresultset As String = Dts.Variables("resultset1").Value.ToString
        MsgBox(outresultset)
 
        Dim pathf As String = Dts.Variables("testv").Value.ToString
        Dim fs As StreamWriter = File.CreateText(pathf + ".txt")
        fs.WriteLine(outresultset.ToString())
        Dts.TaskResult = Dts.Results.Success
	End Sub
 
--i did the script like this

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
PedroCGDCommented:
OK.. I will create a script...
You should use EventHandlers (On Error) to be executed in the case of an unexpected error. This could be applied to all package or to a specific component.
Regards!
Pedro
0
 
brahma007Author Commented:
Hi PedroCGD,

I am so thankful to you.
so you are saying that i need to use eventhandler to send mail.
so is it possible to send messages from Execute SQL task to flat file using script task
my TL said we need to check for error message 'msg (space)' (because error messages start with 'msg (space)'  from flat file and if anythign found then next task should not run and send mail.
how to check flat file using script task one output messages written to flat file.
i want to conform that if we get error message while running script from package will it ignore the error message and continue. as of my knowledge pacage will stop running even oe error come.

Thanks,
Brahma
0
 
brahma007Author Commented:
i forgot menction PedroCGD
 scripts don't give any result sets like set of outputs so output messages are different than output results


Thanks,
Brahma
0
 
PedroCGDCommented:
What I wrote is that you can use EventHandlers to catch unexepected errors/wraining/informations... and send an email or do something else.

Ok... give us a sequence of what you need and we'll try to give you the best solution (in our opinion and not knowing all your requirements)

Regards!
Pedro
www.pedrocgd.blogspot.com
0
 
brahma007Author Commented:
PedroCGD,

i am sorry i did not menction clearly before

here is the steps for automation
1) i need to run scripts in order of directory
2)run script
3)save output to any directory with same filename
4)check for "msg  " and if any "msg  " stop running next script and send mail to DBA
5)run next script

for this i planned to write a package
please give whole idea and any scripts how to make thing working

Thanks,
Brahma
0
 
PedroCGDCommented:
ok... tell me waht you have done until the moment.
Next, the message you need is from the execution os SQL Script? correct?
COuld you give me one script example that runs here in my machine to test?...
if the string returned by SQL has "msg " that means an error, correct?!
Regards!
0
 
brahma007Author Commented:
hi PerdoCGD,

i did nothing related with checking file for "msg ".
i already menctioned my script in previous posts
yah you are right if output message contains "msg " then script completed with errors
(errors may be anything like primary key voliation or anything but just need to find error message in output messages and if anything we need send mail and stop the package to run next script)
the above logic automatically work, i think we dont need to stop package by writing script because already package had some properties if error occurs.
so u just give me script to write output messages  to flat file using script task
i am sorry to make u confused

thanks in advacne

Regards,
Brahma
0
 
PedroCGDCommented:
are you already getting the message returned from the script?
0
 
brahma007Author Commented:
no
with my script above iam just creating empty file nothing written to that file
that what my problem is

thanks,
Brahma
0
 
PedroCGDCommented:
so... the problem IS:

Getting the returned message from a SQL statment, correct?
And export it to a textfile... and in case of an error so something else like send email... correct?

0
 
brahma007Author Commented:
exactly
you have any solution

Thanks,
Brahma
0
 
PedroCGDCommented:
could you pass the message returned by teh SQL statment as a OUTPUT Parameter?
Is a stored procedure the SQL Statment?
0
 
brahma007Author Commented:
i am running scrpt file not a stored procedure
when i tried see the resultset ng script task
it display as System._ComObject
can we capture the output messages from result set in Execute SQL task

Thanks,
Brahma
0
 
PedroCGDCommented:
Dear Friend,
I create a project that without using SQL task, I use Script task.

Ass the attaches package to your SSIS package
update de connection cmSQL and update the SSIS global variable strSQL.
It works?

regards!
Pedro
www.pedrocgd.blogspot.com
Package-EE44-dtsx.txt
SSIS-Script.JPG
0
 
brahma007Author Commented:
in this script how to send message to flat file like how many rows updated or update not done
can plz tell me how to write messages to flat file using packages
for example: 2 row(s) updated
or
Msg 207, Level 16, State 1, Line 1
Invalid column name 'dds'.

thanks,
brahma
0
 
HoggZillaCommented:
Pedro, nice work here!!!
0
 
nmcdermaidCommented:
Just to take a different line here,
In your original post, you were already using the -o parameter with sqlcmd, which means you were already outputting the results of your script to a file.
All you have to do is add to your original script to parse your output file for "msg " and stop if necessary.
You can write some batch script (which you can run using xp_cmdshell) which checks the output file and then sends an email if it finds it. If you are interested in this option then I an find a sample of what you need (in the EE DOS area)
 
This is a good opportunity for you to learn SSIS but it takes a little while to get up the learning curve, and it may be a little too complex for your needs, considering you probably only have to add one line to your existing script.
0
 
brahma007Author Commented:
hi nmcdermaid,
please help me
how to complete this task


Regards,
BRahma

0
 
HoggZillaCommented:
Please assign points to Pedro. Here is the Script Task to write to a file. Put the value you want to write in the file into a variable. Make the variable a ReadOnlyVariable for the Script Task. Here is the Code.
' 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.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain
 
	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.
 
	Public Sub Main()
		'
        ' Add your code here
        Dim Contents As String = "Hello World!"
 
        'If you were passing the file contents in through a variable
        'Contents = Dts.Variables("ReadOnlyVariableName").Value.ToString()
 
        'Put the \ in your files with an escape of \. Thus C:\Temp is C:\\Temp
        WriteContents("C:\\Temp\\fileout.txt", Contents)
 
        Dts.TaskResult = Dts.Results.Success
    End Sub
 
    Public Sub WriteContents(ByVal filePath As String, ByVal contents As String)
 
        Dim objWriter As IO.StreamWriter
        Try
            objWriter = New IO.StreamWriter(filePath)
            objWriter.Write(contents)
            objWriter.Close()
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
    End Sub
 
End Class

Open in new window

0
 
nmcdermaidCommented:
To continue with the batch script option you first need to verify that your original script is writing the output files that you expect to C:\SQL Scripts\
I assume these are the output files that will contain "MSG " in them. You need to first verify this before the batch scripting option is viable.
Alternatively continue with Pedro's solution.
0
 
PedroCGDCommented:
HoggZilla,
Thanks for you comments. :-)

brahma007,
What you need right now is only to write in textfile the output, right?
Do you me to complete my first draft with the for each loop and the script of Hoggzilla?

Regards!
pedro!
0
 
brahma007Author Commented:
Hi PedroCGD,

do something and help me

thanks,
Brahma
0
 
PedroCGDCommented:
Do something? Are you thinking that I'm not doing nothing? eheheh :-)
OK... I updated the package with the HoggZilla script and some more little things...

I wrote a wrong SQL query to generate the error:
The output comes like:
System.Data.SqlClient.SqlException: Invalid object name 't_data_notikficacao'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at ScriptTask_dfd2e7e53a8f4401a7e3ca69b5fd9787.ScriptMain.Main()


Where the message from SQL is Invalid object name 't_data_notikficacao'.
HELPED?
Regards!
Package-EE44-dtsx.txt
0
 
brahma007Author Commented:
Hi PedroCGD,
you got me wrong.
i didn't say ur did nothing
what i mean is please help me out
let me try ur package

Thanks,
Brahma
0
 
PedroCGDCommented:
I KNOW!!! HEHEHE JUST KIDDING WITH YOU!
0
 
brahma007Author Commented:
hi experts,

my problem is not solved in what way i want
once again i explain what i want
1) i need to run scripts in order of directory
2)run script
3)save output to any directory with same filename
4)check for "msg  " and if any "msg  " stop running next script and send mail to DBA
5)run next script

so i need to write the messages not the output to a flat file and then i need t check the whole file for "msg " and if anything found i need to stop executing next script file adn send mail

i am sorry if i made u confused with my explanation. pedroCGD, can you please explain how to modify ur pacakge to meet above requirement

Thanks in advance
0
 
PedroCGDCommented:
I understood, but I dont think you can get the outout formated as is in SQL...
As you can see for attached image, the message is the same I get in SSIS, but without the first lin (MSG....)

But your goal is not to catch the error that a script could generate and forward it by email to the DBA?
Why you need MSG?
What you need is the error and the script that failed, no?
regards!
pedro
Message.JPG
0
 
brahma007Author Commented:
ok let me explain this way
if i run a script i get messages not results and i want to store messages to flat fole
messages mixup with error msgs if any errors exist in script

so is there any chance to get the messages in packages
0
 
nmcdermaidCommented:
3)save output to any directory with same filename
You are already doing his in the original script that you posted at the very start.
I'll leave this in Pedros capable hands I think.
0
 
PedroCGDCommented:
add these lines to the code of first script in the example I gave to you... is the last option I can gice to you:
            MsgBox(Err.Number)
            MsgBox(Err.Description)
            MsgBox(Err.Source)
            MsgBox(Err.Erl)
           
            MsgBox(Err.GetException.Message)
            MsgBox(Err.GetException.InnerException)
            MsgBox(Err.GetException.ToString)

I dont think that could be possible to get the "MSG 208, Level 16...." as you want. I'v talked with some DBA guys and they told me also that is impossible...

Script.JPG
0
 
PedroCGDCommented:
Improvements?!
0
 
brahma007Author Commented:
Hi pedroCGD,

i  think its not possible to getthe messages from execute sql task.
we can get output through result set but not messages
i tried to do with batch scripting there i am intermediate level in batch scripting
so i did not get yet
i am working on trace files. i had trace files before rebuild index and after rebuild index .
obviously queries executing faster after rebuilding but want to know what makes performance improvement by studying these trace files
so if u have anything related to this plz let me know

thanks,
brahma
0
 
PedroCGDCommented:
ok.. set this question as answer.
I will take a look to trace files.
Regards!
pedro
0
 
nmcdermaidCommented:
brahma007
go and look at your very original script that you posted right at the start.
You are running sqlcmd and you are using the -o parameter with it.
This means 'run the script and send its output to a file'
That ouptut file is going to contain your output messages. Can you verify for me whether this is the case? If so we can proceed to extract your required messages from it.

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 15
  • 15
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now