Cannot display/return SQL Query Output from a Variable in DTS

I have been having a very difficult time trying to get the output of a
sql query that is in a dts global variable to return either into a
msgbox or for populating a portion of the body of a mail task.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. Four rows are returned. Basically, I wish to put these
results in the body of a mail task or right now I would settle for a
msgbox just to see it work.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
to write the recordset to a file.
I feel like I am going about this all wrong and the answer is just
staring me in the face and I dont get it.
I welcome suggestions and comments on how to achieve the goal of putting
the query results into the email body.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You may want to use call xp_sendmail directly (see Books online) rather than
the DTS Send Mail task. If not this may help:
http://www.sqldts.com/default.aspx?235
John
"SJM" <nospam@.devdex.com> wrote in message
news:O8fvDgusFHA.596@.TK2MSFTNGP12.phx.gbl...
>I have been having a very difficult time trying to get the output of a
> sql query that is in a dts global variable to return either into a
> msgbox or for populating a portion of the body of a mail task.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. Four rows are returned. Basically, I wish to put these
> results in the body of a mail task or right now I would settle for a
> msgbox just to see it work.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from dotnetbips.com/displayarticle.aspx?id=228 and I was unable
> to write the recordset to a file.
> I feel like I am going about this all wrong and the answer is just
> staring me in the face and I don't get it.
> I welcome suggestions and comments on how to achieve the goal of putting
> the query results into the email body.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Indeed, I am considering sending the mail from within the script, but
right now I am concentrating on getting the data and other info into the
message body. Thanks for the suggestion.
*** Sent via Developersdex http://www.examnotes.net ***sql

Cannot display/return SQL Query Output from a Dispatch Variable

I have been having a very difficult time trying to get the output of a
sql query that is in a DTS global variable to return, presently to a
messagebox.
I created an Execute SQL task with the query I wish to use. The query
has been tested in Query Analyzer and works fine and returns the results
I am looking for. I set a global variable to the result set. Basically,
I wish to display the results as a string or similar in a messagebox.
I have created the Execute SQL task as described in
http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
I have tried to retrieve the results using the example in
http://msdn.microsoft.com/library/d...y/en-us/howtosq
l/ht_dts_task_6llt.asp but I have been unable to do so.
I also tried the GetString method of the recordset without success.
I also tried the using the Storing the resultset in a flat file
example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
unable to write the recordset to a file.
What is the proper way to display a variable of type dispatch? I feel
like I am going about this the wrong way.
I welcome suggestions and comments on how to achieve the goal of
displaying the query results.
Thanks.
*** Sent via Developersdex http://www.examnotes.net ***Hi
You don't seem to have:
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
listed.
John
"SJM" <nospam@.devdex.com> wrote in message
news:u6hU08ItFHA.304@.TK2MSFTNGP11.phx.gbl...
>
> I have been having a very difficult time trying to get the output of a
> sql query that is in a DTS global variable to return, presently to a
> messagebox.
> I created an Execute SQL task with the query I wish to use. The query
> has been tested in Query Analyzer and works fine and returns the results
> I am looking for. I set a global variable to the result set. Basically,
> I wish to display the results as a string or similar in a messagebox.
> I have created the Execute SQL task as described in
> http://msdn.microsoft.com/library/e...s_task_4gkl.asp .
> I have tried to retrieve the results using the example in
> http://msdn.microsoft.com/library/d...y/en-us/howtosq
> l/ht_dts_task_6llt.asp but I have been unable to do so.
> I also tried the GetString method of the recordset without success.
> I also tried the using the "Storing the resultset in a flat file"
> example from http://dotnetbips.com/displayarticle.aspx?id=228 and I was
> unable to write the recordset to a file.
> What is the proper way to display a variable of type dispatch? I feel
> like I am going about this the wrong way.
> I welcome suggestions and comments on how to achieve the goal of
> displaying the query results.
> Thanks.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, that MSDN is what I followed to get the data into the variable,
getting it out is the problem.
The workaround I am using is to execute the SQL statements in the
VBscript and not use the execute SQL DTS task. For example:
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL STATEMENT HERE")
if rs.eof and rs.bof then
msgbox "Error: No records"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
msgbox " " & rs.Fields(intLoop).Name
Next
set rs = rs.Next
loop
end if
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||
Everything is fine with the code listed below. I am going to use this
instead of the DTS execute sql task.
Option Explicit
Function Main()
dim cnn
dim rs
dim intLoop
dim strText
set cnn=createobject("ADODB.Connection")
cnn.Open "Provider=sqloledb;" & _
"Data Source=SERVER;" & _
"Initial Catalog=msdb;" & _
"Integrated Security=SSPI"
set rs=cnn.execute("SQL QUERY")
If rs.BOF then
msgbox "No Records Found"
else
do until rs.EOF
For intLoop = 0 To rs.Fields.Count - 1
strText = strText & rs.fields(intLoop).value & " " & vbCrLf
Next
rs.MoveNext
loop
msgbox strText
end if
set rs = nothing
Main = DTSTaskExecResult_Success
End Function
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
It was the wrong link, should have sync'd
http://msdn.microsoft.com/library/d...asp?frame=true
I have just followed
http://msdn.microsoft.com/library/d...>
ask_4gkl.asp
and the above and there have been no problems.
John
"SJM" <nospam@.devdex.com> wrote in message
news:euD9JTUtFHA.2624@.TK2MSFTNGP12.phx.gbl...
>
> Everything is fine with the code listed below. I am going to use this
> instead of the DTS execute sql task.
> Option Explicit
> Function Main()
> dim cnn
> dim rs
> dim intLoop
> dim strText
> set cnn=createobject("ADODB.Connection")
> cnn.Open "Provider=sqloledb;" & _
> "Data Source=SERVER;" & _
> "Initial Catalog=msdb;" & _
> "Integrated Security=SSPI"
> set rs=cnn.execute("SQL QUERY")
> If rs.BOF then
> msgbox "No Records Found"
> else
> do until rs.EOF
> For intLoop = 0 To rs.Fields.Count - 1
> strText = strText & rs.fields(intLoop).value & " " & vbCrLf
> Next
> rs.MoveNext
> loop
> msgbox strText
> end if
> set rs = nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> *** Sent via Developersdex http://www.examnotes.net ***

Cannot continue past Source File Error...

I have an SSIS package where I have directed the error output to a Flat File Destination. The issue is that there are some bad entried in a set of log files, where the source file reads on more delimited column than there are actual columns. (As in there are 26 column headers, and one row will have 27 commas, or delimiters.) I am trying to redirect the row output to put the bad rows into a Flat File for debugging purposes. Although, the package is not able to continue past the error. As soon as it hits the bad row, it fails despite the error output.

Any ideas?

This wouldn't cause a problem exactly as you stated it. If you had a row that contained more delimiters than defined columns, the Flat File Source adapter will just ignore the extra delimiter on the last column. [Value of last column would be "ABCDEF,12345, FEDCBA"]. However, if the extra delimiter is in the middle of the row and shifts columns causing a defined text column to move to a defined numeric or date column, the input source will fail validation and cause the package to fail validation.

Had a similar issue with .csv files and had to resort to using a Script component as a source component instead of a Flat File Source but you have know some .NET to use that approach. It's incredibly powerful.

|||

Thanks for the reply!

I believe I have isolated the issue to a particular value in the csv I'm importing. It breaks with a quote, semi-colon, and then some more data, which is what I believe throws the error. Is there a way to have multiple text qualifiers, or tell the Flat File Source to only look for the qualifier if it is next to a comma(delimiter)?