August 29, 2011 at 8:52 am
Hi,
I have a script task as a lat task in my package. Here I am trying to read the error log table and give the count in the msgbox. The execution giving error at this task (with red color). But, execution results is not giving any details about the error.
I have started my task with simple code below:
Dim connMgr As ConnectionManager = Dts.Connections("AuthenCustomer")
Dim ConnStr As String = connMgr.ConnectionString
Dim sqlConn As SqlConnection = CType(connMgr.AcquireConnection(Nothing), SqlConnection) ' Object to hold connection
Dim sqlCmd As SqlCommand ' SQL Command object
Dim rdrReader As SqlDataReader ' Data reader to hold output of command
Dim sqlCmdText As String ' SQL Command text
Try
sqlCmdText = "select count(*) from SSISErrorLog"
sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
rdrReader = sqlCmd.ExecuteReader()
MsgBox(rdrReader.Item(0), , "The table contains this many rows:")
rdrReader.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Please let me know, what is wrong in this code.
August 29, 2011 at 9:21 am
Don't you need to convert the integer count to a string ? myRdr.Item(0).ToString() ??
August 29, 2011 at 9:39 am
Thank you Vishal. But, I have added that also. Even in the begining only, now added 'MessageBox.Show("Start query")'. It is not displaying thi smessage even. Thank you for your help.
August 29, 2011 at 9:43 am
Not sure if this helps, but in c# the way to get a string value from a reader is
myReader.GetValue(0).ToString();
and the SSIS script you have is in VB, so .. not sure but is that (.item(0)) correct syntax?
August 29, 2011 at 9:48 am
I have tried that syntax also. No syntax error. Same task error but no description.
Thank you.
August 29, 2011 at 10:04 am
I think there is something wrong with the way the connection string is initialized and used.
You may need to either specify the full connection string or use something like this:
DirectCast(Connections.AuthenCustomer.AcquireConnection(Nothing), SqlConnection)
More info:
August 29, 2011 at 10:23 am
Thank you so much for the quick responses. I have followed the link and everyhting looks similar except the connection string. The finalized code is here for your review:
Dim connMgr As ConnectionManager = Dts.Connections("AuthenCustomer")
Dim ConnStr As String = connMgr.ConnectionString
Dim sqlConn As SqlConnection = CType(connMgr.AcquireConnection(Nothing), SqlConnection) ' Object to hold connection
Dim sqlCmd As SqlCommand ' SQL Command object
Dim rdrReader As SqlDataReader ' Data reader to hold output of command
Dim sqlCmdText As String ' SQL Command text
Public Sub Main()
Try
MessageBox.Show("Before connections")
sqlConn.ConnectionString = "Data Source=sqldev01;Initial Catalog=AuthenCustomer;Integrated Security=True;"
' Set the command text
'sqlCmdText = "select vchSourceName, iErrorCode, vchErrorDescription, vchCustomDescription, vchData from SSISErrorLog"
sqlCmdText = "select count(*) from SSISErrorLog"
' Execute the command and put the results into the data reader
sqlCmd.Connection = sqlConn
sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
rdrReader = sqlCmd.ExecuteReader()
' Take the data item in the reader and show it in a message box
'Do While rdrReader.Read()
MessageBox.Show(rdrReader.GetValue(0).ToString())
'Loop
rdrReader.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
MessageBox.Show("Failure")
End Try
End Sub
August 29, 2011 at 11:34 am
Did you try getting the error message?
Change your error handler part to this:
Catch ex As Exception
MessageBox.Show("Failure: " + ex.Message.ToString())
Dts.TaskResult = Dts.Results.Failure
End Try
August 29, 2011 at 11:38 am
Sorry to say, no use. May be it is connection problem.
Thank you
August 29, 2011 at 11:44 am
Try changing it to this:
Public Sub Main()
Try
MessageBox.Show("Before connections")
Dim connMgr As ConnectionManager = Dts.Connections("AuthenCustomer")
Dim ConnStr As String = connMgr.ConnectionString
Dim sqlConn As SqlConnection = CType(connMgr.AcquireConnection(Nothing), SqlConnection) ' Object to hold connection
Dim sqlCmd As SqlCommand ' SQL Command object
Dim rdrReader As SqlDataReader ' Data reader to hold output of command
Dim sqlCmdText As String ' SQL Command text
sqlConn.ConnectionString = "Data Source=sqldev01;Initial Catalog=AuthenCustomer;Integrated Security=True;"
' Set the command text
'sqlCmdText = "select vchSourceName, iErrorCode, vchErrorDescription, vchCustomDescription, vchData from SSISErrorLog"
sqlCmdText = "select count(*) from SSISErrorLog"
' Execute the command and put the results into the data reader
sqlCmd.Connection = sqlConn
sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
rdrReader = sqlCmd.ExecuteReader()
' Take the data item in the reader and show it in a message box
'Do While rdrReader.Read()
MessageBox.Show(rdrReader.GetValue(0).ToString())
'Loop
rdrReader.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
MessageBox.Show("Failure: " + ex.Message.ToString())
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub
And also, paste what your output log is providing after you execute it with the above changes.
August 29, 2011 at 11:52 am
It didn't work. As I am new to SSIS, no idea where to look for log information.
Thanks a lot.
August 29, 2011 at 11:53 am
It didn't work. As I am new to SSIS, don't know where to look for log information.
Thanks a lot.
August 29, 2011 at 11:58 am
You need to explain more what you mean by "It didn't work"
What are you getting as an output? Do you have any message box appear when you execute the package?
After your package completes execution, there should be a tab where you can view the output. Look at the attached screen shot if you have trouble finding it. Paste all the lines in that output box into here.
August 29, 2011 at 2:55 pm
I found the solution by specifically declaring the connection as below:
Dim sqlConn As New SqlConnection("server=sqldev01.lac.authenticom.com;uid=Authencustomer;pwd=Test1234;database=AUTHENCustomer")
Thanks
August 30, 2011 at 8:51 am
You probably should remove that info ...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply