February 28, 2020 at 6:35 pm
Can anyone help me I'm trying to run a SSIS package and it keeps failing I'm trying to execute 7 days of data from a table. can anyone help me
declare
@nOrgID int,
@startdate date,@enddate date,
@nORGID int = 67,@nDonationTypeID int = 4
@nDonationTypeID
set @startdate = DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk, 0,getdate()), -1))-- for saturday
set @enddate = DATEADD(wk, DATEDIFF(wk, 0, getdate()), -2) --for sunday
select @startdate,@enddate
select nStartLocationID,nEndLocationID,nDonorCount,tEffectiveDate,cChangedBy
from Data_Org_Location_Donations
where nOrganizationID = @nORGID
and nDonationTypeID = @nDonationTypeID
and nactive=1
and tEffectiveDate between @startdate and @enddate
ORDER BY tEffectiveDate
This is the error I'm getting when I run my package Im trying to pull a week of data.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "declare
@nOrgID int,
@startdate date,@enddate dat..." failed with the following error: "Must declare the scalar variable "@nORGID".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\EdsTestPackages\ForLoop\DCWeeklyFinal\Project DC Weekly Final\Project DC Weekly Final\Package.dtsx" finished: Failure.
The program '[5712] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
February 28, 2020 at 7:22 pm
When I reformat your code to be more readable - this is what I get:
Declare @nOrgID int
, @startdate date
, @enddate date
, @nORGID int = 67
, @nDonationTypeID int = 4
@nDonationTypeID
Set @startdate = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday
Set @enddate = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday
Select @startdate
, @enddate
Select nStartLocationID
, nEndLocationID
, nDonorCount
, tEffectiveDate
, cChangedBy
From Data_Org_Location_Donations
Where nOrganizationID = @nORGID
And nDonationTypeID = @nDonationTypeID
And nactive = 1
And tEffectiveDate Between @startdate And @enddate
Order By
tEffectiveDate
Notice here that you have declared @nOrdId and @nORGID both - which would only be valid if your server uses a case-sensitive collation.
Also notice that @nDonationTypeID is actually not declared...
It is also recommended for SSIS that you include the command:
Set Nocount On;
at the beginning to eliminate the results from that command...which leads to the last issue where you are returning this:
Set @startdate = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday
Set @enddate = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday
Select @startdate
, @enddate
That becomes the second resultset that is going to be returned from this query - the first being the count, the second the above and the third resultset is the actual query.
Remove the duplicate variable(s) - add the nocount and remove the extra select statement and it should work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2020 at 7:35 pm
Hi Thank you for youre respone,
I was wondering can you type out the correct format that it suppose to be I'm new to sql so I just want to try the correct code.
February 28, 2020 at 7:44 pm
You can try running this:
Declare @nOrgID int = 67
, @startdate date = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday
, @enddate date = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday
, @nDonationTypeID int = 4;
Select ld.nStartLocationID
, ld.nEndLocationID
, ld.nDonorCount
, ld.tEffectiveDate
, ld.cChangedBy
From dbo.Data_Org_Location_Donations ld
Where ld.nOrganizationID = @nORGID
And ld.nDonationTypeID = @nDonationTypeID
And ld.nactive = 1
And ld.tEffectiveDate Between @startdate And @enddate
Order By
ld.tEffectiveDate;
I did not review whether or not your calculation for start/end is correct though...
Also I would not use BETWEEN with dates - I would recommend using an open-interval range check as in:
And ld.tEffectiveDate >= @startdate
And ld.tEffectiveDate < dateadd(day, 1, @enddate)
If tEffectiveDate includes the time, using between could miss the end of the week's data because your date would be implicitly converted to a datetime with the time as 00:00:00.000. If the column is defined as a date data type then using between will work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 28, 2020 at 7:47 pm
ok I fixed it and I got this output:
---------- ----------
2020-02-16 2020-02-22
nStartLocationID nEndLocationID nDonorCount tEffectiveDate cChangedBy
---------------- -------------- ----------- ----------------------- --------------------------------------------------
but I have nothing from the table in there showing but I also I want to send this as an attachment with send mail can you help me with this as well?
my profile name for my db send mail is WESA DC Weekly
February 29, 2020 at 3:43 pm
Here is a template I use for sending email notifications. This embeds an HTML table in the email message...
Set Nocount On;
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = '{semi-colon delimited list of email accounts}'
, @cc_recipients varchar(max) = '{semi-colon delimited list of email accounts}'
, @startDate datetime
, @endDate datetime;
Declare @nOrgID int = 67
, @startdate date = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday
, @enddate date = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday
, @nDonationTypeID int = 4;
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
, html.hdr4 As th, ''
, html.hdr5 As th, ''
From (
Values ('Start Location', 'End Location', 'Donor Count', 'Effective Date', 'Changed By')
) As html(hdr1, hdr2, hdr3, hdr4, hdr5)
For xml Path('tr'), elements) As varchar(max));
Set @xmlResults = cast((Select ld.nStartLocationID
, ld.nEndLocationID
, ld.nDonorCount
, ld.tEffectiveDate
, ld.cChangedBy
From dbo.Data_Org_Location_Donations ld
Where ld.nOrganizationID = @nORGID
And ld.nDonationTypeID = @nDonationTypeID
And ld.nactive = 1
And ld.tEffectiveDate Between @startdate And @enddate
Order By
ld.tEffectiveDate
For xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
Set @body += 'th {font-size:10pt; text-align:left; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body>{ enter you body text here }
';
--==== Setup the table with the list of new document types
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{your profile here}'
, @from_address = '{some from address here - does not have to actually exist}'
, @reply_to = '{your reply to address here - must exist as a valid address}'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @subject = '{your subject}'
, @body_format = 'HTML'
, @body = @body;
End
Go
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 29, 2020 at 3:48 pm
Here is a sample where the file is attached - and it will be sent regardless of any results found:
Declare @tab char(1) = char(9)
, @query nvarchar(max) = ''
, @recipients varchar(max) = '{list of email addresses}'
, @cc_recipients varchar(max) = '{list of email addresses}';
Set @query = '
Set Nocount On;
Declare @nOrgID int = 67
, @startdate date = dateadd(wk, -1, dateadd(wk, datediff(wk, 0, getdate()), -1))-- for saturday
, @enddate date = dateadd(wk, datediff(wk, 0, getdate()), -2) --for sunday
, @nDonationTypeID int = 4;
Select ld.nStartLocationID As [Sep=,' + char(13) + char(10) + 'StartLocationID]
, ld.nEndLocationID
, ld.nDonorCount
, ld.tEffectiveDate
, ld.cChangedBy
From dbo.Data_Org_Location_Donations ld
Where ld.nOrganizationID = @nORGID
And ld.nDonationTypeID = @nDonationTypeID
And ld.nactive = 1
And ld.tEffectiveDate Between @startdate And @enddate
Order By
ld.tEffectiveDate;'
Execute msdb.dbo.sp_send_dbmail
@profile_name = '{your profile here}'
, @query = @query
, @subject = '{your subject here}'
, @body = '{your body message here}'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = '{database where this needs to run}'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = '{file name to be attached as}'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;
This sends as a CSV file and will open directly in Excel when double-clicked in Outlook as an attachment.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 29, 2020 at 6:06 pm
Hi Jeff
so how would I put this in my send mail task in my ssis package are you familiar with ssis packages ?
March 1, 2020 at 5:32 pm
Both of those are not meant for SSIS - they are simple agent jobs that will do the work, no need for SSIS at all.
If you have to do this in SSIS then you would create a data flow that creates a file and then use the send mail task in SSIS to send the file as an attachment. I would not try to utilize SSIS to embed data in the send email task...it requires utilizing a script task and a lot of code. Here is an example: https://www.mssqltips.com/sqlservertip/4306/send-multiple-query-result-sets-in-html-tabular-format-in-a-single-email-with-sql-server-integration-services/
Also - you did not remove the select for the start/end dates. That must be removed or SSIS will attempt to process that as the resultset and you won't get any output.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 2, 2020 at 9:18 pm
I was having browser. Issues so do I run the command option in the Data flow tas I just need a outline
March 2, 2020 at 9:23 pm
How is my stored procedure going to look like ?
March 2, 2020 at 10:38 pm
Neither of the examples are meant to be run from SSIS - they are meant to be used in SQL Server Agent. They can be included directly in the job step as is (after replacing the values in {}) - or you can create a stored procedure and just call the stored procedure.
I cannot tell you how the stored procedure would look...it depends on what you want to include as parameters and options for the code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 2, 2020 at 10:59 pm
Do you have a email ?
March 2, 2020 at 11:07 pm
Thank you now will this put the data it’s calling in the file as well ?
thank you
March 3, 2020 at 1:50 am
Hi Jeff,
I can't find the syntax errors in this can you take a peak for me I'm new to scripting in sql
Keeps complaining about this:
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '{'.
Msg 105, Level 15, State 1, Line 40
Unclosed quotation mark after the character string ',
@query_result_no_padding = 1;
Declare @tab char(1) = char(9) , @query nvarchar(max) = '' , @recipients varchar(max) = '{edward.vahovick@gimi.org}' , @cc_recipients varchar(max) = '{list of email addresses}';
Set
@query = '
Set
Nocount
On;
Declare @nOrgID int = 67 , @startdate date = dateadd(wk, - 1, dateadd(wk, datediff(wk, 0, getdate()), - 1))-- for saturday
, @enddate date = dateadd(wk, datediff(wk, 0, getdate()), - 2) --for sunday
, @nDonationTypeID int = 4;
Select
ld.nStartLocationID As [Sep =,
' + char(13) + char(10) + 'StartLocationID],
ld.nEndLocationID,
ld.nDonorCount,
ld.tEffectiveDate,
ld.cChangedBy
From
dbo.Data_Org_Location_Donations ld
Where
ld.nOrganizationID = @nORGID
And ld.nDonationTypeID = @nDonationTypeID
And ld.nactive = 1
And ld.tEffectiveDate Between @startdate And @enddate
Order By
ld.tEffectiveDate;
'
Execute msdb.dbo.sp_send_dbmail @profile_name = '{WESA DC Weekly
,
@query = @query,
@subject = '{Weekly dc transfers}',
@body = '{Hello this is a test}',
@recipients = @recipients,
@copy_recipients = @cc_recipients,
@execute_query_database = '{WESA_3_Production}',
@attach_query_result_as_file = 1,
@query_result_width = 8000,
@query_attachment_filename = '{dcweekly.csv}',
@query_result_header = 1,
@query_result_separator = ',',
@query_result_no_padding = 1;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply