Dates Are Missing Seconds
-
Hi
I have two tables that get dates created via SSIS C# script. The first table has the seconds but the second one doesn't. I am not sure why the second table is missing the seconds. I have attached a picture. The first table has the date in the file name so you can compare the two outputs this way.
The second table is missing seconds:
The SSIS C# script runs two stored procedures.
This stored procedure is used to update the table from the first output in the picture above:
ALTER PROCEDURE [dbo].[UpdateImportHistory] (@FILENAME VARCHAR(100), @RECORD_ADD_DATE datetime)
AS
BEGIN
INSERT INTO DBO.DataImportFileImportHistory
VALUES (@FILENAME, @RECORD_ADD_DATE);
ENDThis stored procedure produces the output from the second output from the above picture:
ALTER PROCEDURE [dbo].[UpdateOptionsEOD] (@FILENAME varchar(200), @RECORD_ADD_DATE datetime)
AS
DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME
DECLARE @RECORD_ADD_DT datetime
SET @RECORD_ADD_DT = @RECORD_ADD_DATE
DROP TABLE IF EXISTS #TEMP_TABLE;
CREATE TABLE #TEMP_TABLE
(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
more fields
);
EXEC('BULK INSERT #TEMP_TABLE
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + @RECORD_ADD_DT +
''' AS TIME_STAMP
FROM #TEMP_TABLE'
)
DROP TABLE #TEMP_TABLEThe above two stored procedures have a parameter @RECORD_ADD_DATE. This parameter is populated via C#. The variable Record_Add_Dt is of type DATETIME. It is assigned once with NOW and then Record_Add_Dt is used as a parameter for both stored procedures:
public void Main()
{
// TODO: Add your code here
try
{
string filePathTemp = @"C:\Users\Me\Trading_Data\Temp_Files\";
string filePathSource = @"C:\Users\Me\Trading_Data\To_Be_Processed\";
string filePathDestination = @"C:\Users\Me\Trading_Data\Processed_Files\";
string filePathError = @"C:\Users\Me\Trading_Data\Failed_Files\";
string[] fileEntries = Directory.GetFiles(filePathTemp, "*");
DateTime Record_Add_Dt = new DateTime();
string connectionString = @"Data Source=localhost;Initial Catalog=testDB;Integrated Security=True";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
foreach (string fullFileName in fileEntries)
{
if (fullFileName.Contains("Options"))
{
SqlCommand cmd = con.CreateCommand();
SqlTransaction transaction;
transaction = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = transaction;
string fileName = "";
string fileNameError = "";
try
{
Record_Add_Dt = DateTime.Now;
string SQL = "dbo.UpdateOptionsEOD";
cmd = new SqlCommand(SQL, con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1, param2;
param1 = cmd.Parameters.Add("@FILENAME", SqlDbType.VarChar, 200);
param1.Value = fullFileName;
param2 = cmd.Parameters.Add("@RECORD_ADD_DATE", SqlDbType.DateTime);
param2.Value = Record_Add_Dt;
fileName = fullFileName.Substring(filePathTemp.Length, fullFileName.Length - filePathTemp.Length).Trim();
fileName = (fileName.Substring(0, fileName.Length - 4) + ".zip").Trim();
fileNameError = (fileName.Substring(0, fileName.Length - 4) + "_" + Record_Add_Dt.ToString("yyyy-dd-MM--HH-mm-ss") + ".zip").Trim();
cmd.ExecuteNonQuery();
SQL = "dbo.UpdateImportHistory";
cmd = new SqlCommand(SQL, con, transaction);
cmd.CommandType = CommandType.StoredProcedure;
param1 = cmd.Parameters.Add("@FILENAME", SqlDbType.VarChar, 100);
param1.Value = fileName;
param2 = cmd.Parameters.Add("@RECORD_ADD_DATE", SqlDbType.DateTime);
param2.Value = Record_Add_Dt;
cmd.ExecuteNonQuery();Each of the tables that are updated have the respective date field to be type datetime:
CREATE TABLE [dbo].[OptionsEOD](
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
many fields
[TIME_STAMP] [datetime] NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[DataImportFileImportHistory](
[FILENAME] [varchar](100) NOT NULL,
[RECORD_ADD_DATE] [datetime] NOT NULL,
CONSTRAINT [PK_DataImportFileImportHistory] PRIMARY KEY NONCLUSTERED
(
[FILENAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GOAnyone have any idea why the second table is missing the seconds?
Thank you
- This topic was modified 1 year, 7 months ago by water490.
-
For more accuracy, you can use Record_Add_Dt = DateTime.UTCNow;
=======================================================================
-
Taking a step back for a moment ...
It seems that you are running an SSIS package which calls a proc which runs BULK INSERT – a rather convoluted design. May I ask why you did it that way rather than using dataflows in SSIS?
Also, did you consider using a FOREACH container to iterate around the files of interest in filePathTemp, and then using parameterised ExecuteSQL tasks to run your procs? I don't think you need that script at all.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Taking a step back for a moment ...
It seems that you are running an SSIS package which calls a proc which runs BULK INSERT – a rather convoluted design. May I ask why you did it that way rather than using dataflows in SSIS?
Also, did you consider using a FOREACH container to iterate around the files of interest in filePathTemp, and then using parameterised ExecuteSQL tasks to run your procs? I don't think you need that script at all.
The C# script I provided is a short excerpt of a much large script. It is easier to handle rollbacks, error handling etc in C#. I am sure SSIS can do it but I do not know how to do that. The C# script does its job so I don't see any reason to fix something that isn't really broken. For example, I need to get data from a SFTP server and SSIS can't do that. There are 3rd party solutions but they are way too expensive. I have programming experience so using C# makes sense to me.
The other reason is experience. I started this project over 2 years ago. At that time I had zero knowledge about SS and SSIS. Over time I have learned a lot. Hindsight is 20/20 and there are things I would do differently. Given that the current solution works I don't see any need to change it. If I start a new project then I will definitely plan it differently.
-
Good answer, makes complete sense, thanks for taking the time to write it out.
There are a couple of points I'd like to mention, however.
First is that SFTP is completely doable in SSIS, without the need to resort to expensive third-party solutions. Take a look at this link and you'll see how.
Second (and I think you might agree) is that I believe the error and transaction handling would be better handled at the database engine layer, as it's 'closer to the metal'. Not SSIS handling it, but the SQL Server database engine.
As you said, now that you know more, you would do things differently. I'm sure that all seasoned developers have experienced this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Good answer, makes complete sense, thanks for taking the time to write it out.
There are a couple of points I'd like to mention, however.
First is that SFTP is completely doable in SSIS, without the need to resort to expensive third-party solutions. Take a look at this link and you'll see how.
Second (and I think you might agree) is that I believe the error and transaction handling would be better handled at the database engine layer, as it's 'closer to the metal'. Not SSIS handling it, but the SQL Server database engine.
As you said, now that you know more, you would do things differently. I'm sure that all seasoned developers have experienced this.
you raise many good points and I agree with you. also, this forum has been fantastic. I have learned so much about SS and SSIS because of the kindness and generosity of the members who take the time to share their knowledge to help rookies like me learn. big thank you to everyone!
Yes I used WINSCP for handling the SFTP portion. cheap and works great! setting it up was a bit tricky but got it working eventually.
Do you have any input on why the table is missing the seconds? It "looks" like it should work and match but clearly not. I have checked everything (already posted above) and everything "looks" ok. this is super puzzling. any help you can offer is much appreciated.
-
Can't see why it's not working, based on the code you've provided.
Why are you doing this in the proc?
DECLARE @RECORD_ADD_DT datetime
SET @RECORD_ADD_DT = @RECORD_ADD_DATESeems pointless – just use @RECORD_ADD_DATE and get rid of @RECORD_ADD_DT.
Have you tried running the code manually in SSMS, including a SELECT from the temp table, in an attempt to isolate where the issue is happening?
I've noticed that your INSERTs do not explicitly name the target columns. This is generally regarded as bad practice and I recommend you start using explicit naming, like this:
INSERT dbo.DataImportFileImportHistory
(
FileName
,Record_Add_Date
)
VALUES
(@FILENAME, @RECORD_ADD_DATE);The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
My guess would be some implicit conversion happening here
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + @RECORD_ADD_DT +
''' AS TIME_STAMP
FROM #TEMP_TABLE'Cant see nothing else which could be doing it.
But converting a DATETIME to a STRING may be loosing some precision.
Try
INSERT INTO DBO.OptionsEOD
SELECT *, ''' + CONVERT(VARCHAR(23),@RECORD_ADD_DT,121) +
''' AS TIME_STAMP
FROM #TEMP_TABLE'Does this work for you at all?
-
your query does not tell us which table has the wrong data.
but assuming it is DBO.OptionsEOD try the following change on your dynamic SQL
EXEC('BULK INSERT #TEMP_TABLE
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
INSERT INTO DBO.OptionsEOD
SELECT *, convert(datetime, ''' + convert(char(26), @RECORD_ADD_DT, 121) + ''', 121) AS TIME_STAMP
FROM #TEMP_TABLE'
)
DROP TABLE #TEMP_TABLE -
thank you everyone. I am getting error when I update the dynamic sql. see below. Any idea on what is wrong?
-
I figured out the problem:
SELECT *, convert(datetime, convert(char(26), ''' + @RECORD_ADD_DT + ''', 121) , 121) AS TIME_STAMP
FROM #TEMP_TABLE'The "" were in the wrong spot.
The time stamp is still missing seconds. So weird:
Yes, I agree. SS is doing some kind of implicit conversion but I not sure why. The only diff between the two sp is that the problem one has this line:
DECLARE @RECORD_ADD_DT datetime
SET @RECORD_ADD_DT = @RECORD_ADD_DATECould this be a problem?
-
can you show both the select statement and the output - only for the one that is failing, nothing else.
-
np
here you go:
stored proc:
ALTER PROCEDURE [dbo].[UpdateOptionsEOD] (@FILENAME varchar(200), @RECORD_ADD_DATE datetime)
AS
DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME
--DECLARE @RECORD_ADD_DT datetime
--SET @RECORD_ADD_DT = @RECORD_ADD_DATE
DROP TABLE IF EXISTS #TEMP_TABLE;
CREATE TABLE #TEMP_TABLE
(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
more fields
);
EXEC('BULK INSERT #TEMP_TABLE
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
UPDATE #TEMP_TABLE
SET UNDERLYING_SYMBOL = ''SPX''
WHERE TRIM(UNDERLYING_SYMBOL) LIKE ''^SPX''
INSERT INTO DBO.OptionsEOD
SELECT *, convert(datetime, convert(char(26), ''' + @RECORD_ADD_DATE + ''', 121) , 121) AS TIME_STAMP
FROM #TEMP_TABLE'
)
DROP TABLE #TEMP_TABLEquery to get data:
selectdistinct QUOTE_DATE, TIME_STAMP
fromdbo.OptionsEOD
order by 1,2output:
-
can you try
select top 1 quote_date, convert(varchar(26), time_stamp, 121)
from dbo.OptionsEOD
order by 1 -
np
here you go
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply