SSIS Question
-
Hi everyone. I am re-writing a portion of my SSIS logic. My SSIS package downloads daily stock market data and then performs calculations for each trading day. Once a particular day's calculations have been done and stored there is no need to re-do this work when new data is added. Only new data should be processed. Version 1 does a full re-calculation of historical data plus new data. This is not efficient. Version 2 only does a calculation of new data and does not touch old data.
Version 1
Use Execute SQL Task Editor to run SP1
Version 2
Use Script Task Editor to run RunStoredProcedures. RunStoredProcedures is a collection of multiple SPs so it includes SP1. SP1 was not modified.
RunStoredProcedures:
RunStoredProcedures
/****** Object: StoredProcedure [dbo].[RunStoredProcedures] Script Date: 2024-10-03 1:52:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[RunStoredProcedures]
as
EXEC dbo.SP1
EXEC dbo.SP2
--truncate table dbo.DataFileProcessingDates
--debugging purposes to test the rollback logic..remove after testing is done
select 1/0Version 2 is producing below lines in SSIS output which I do not see in Version 1. Why are they appearing? How can I fix Version 2 so they no longer appear? SP1 was not modified when Version 1 and Version 2 were run so the issue isn't related to SP1. It is something to do with how the SP was run (my educated guess).
Information: 0x0 at Run Stored Procedures: Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
Information: 0x0 at Run Stored Procedures: Rollback of RunStoredProcedures successfulI am a rookie coder so I am quite sure my C# script below is not optimal. If there are better ways to do the same thing please let me know.
C# Script:
public void Main()
{
// TODO: Add your code here
try
{
string connectionString = @"Data Source=localhost;Initial Catalog=TestDB;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
SqlCommand cmd = con.CreateCommand();
SqlTransaction transaction;
transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
try
{
string SQL = "dbo.RunStoredProcedures";
cmd = new SqlCommand(SQL, con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex1)
{
bool fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Message: {0}", ex1.Message),
null, 0, ref fireAgain);
try
{
transaction.Rollback();
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Rollback of RunStoredProcedures successful"),
null, 0, ref fireAgain);
}
catch (Exception ex2)
{
fireAgain = false;
Dts.Events.FireInformation(0, null,
string.Format("Rollback Exception Type: {0}", ex2.GetType()),
null, 0, ref fireAgain);
}
}
//transaction.Commit();
con.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex3)
{
Dts.Events.FireError(0, "Exception from Script Task", ex3.Message + "\r" + ex3.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}Thank you
-
google is your friend - you should have done it first.
regarding the code and because you got a timeout I would add a command timeout before the executenonquery.
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120; // this is in seconds - adjust as needed. use zero to never timeout
cmd.ExecuteNonQuery(); - frederico_fonseca wrote:
google is your friend - you should have done it first.
regarding the code and because you got a timeout I would add a command timeout before the executenonquery.
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 120; // this is in seconds - adjust as needed. use zero to never timeout
cmd.ExecuteNonQuery();Thanks for the reply.
If I use SET ANSI_WARNINGS OFF then the 3 warnings are gone. That is good to hear so that part of the issue is addressed.
If I use cmd.CommandTimeout = 0 then the rollback logic fails to happen. The SP has a 1/0 line to force a failure so I can test the rollback logic. If I do not use cmd.CommandTimeout = 0 then rollback works fine. The SPs will take a long time to run (depending on how much data there is to process) so having an explicit timeout value is not ideal (unless I am not understanding the function correctly).
Any suggestions on what to do next?
-
First of all, the warning was telling you that you have columns that you're doing aggregates like SUM() and COUNT() on contain some NULL values. Are you sure those columns are supposed to be NULL? If not, then you need to turn warnings back on and go figure out what the problem is with the missing data.
The other thing is... you're doing this all in SSIS.... why are you using C# to execute your stored procedures instead of using SSIS to call them instead of just incorporating them into a process flow (I forget the real name for them in SSIS) ???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
First of all, the warning was telling you that you have columns that you're doing aggregates like SUM() and COUNT() on contain some NULL values. Are you sure those columns are supposed to be NULL? If not, then you need to turn warnings back on and go figure out what the problem is with the missing data.
The other thing is... you're doing this all in SSIS.... why are you using C# to execute your stored procedures instead of using SSIS to call them instead of just incorporating them into a process flow (I forget the real name for them in SSIS) ???
there are a bunch of SPs...some of them are doing prep work for another SP. The warnings are known issues that have been addressed in the SP that consume the output of the prep SPs.
My SSIS package does alot of other data prep so it makes sense to me to use it.
-
Correct Jeff, execute SQL Task is your friend here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply