Hi,
I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.
Any ideas?
Thanks in advance for your help.
$wapnil
Hi
SSIS is brand new for me.. Playing with since a few hours..
Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"
How do i get around this to import the data in a readable fashion into the Destination?
Thanks!
MKR
I have a simple package to load data from sql server db into a flat file. I have a date field in the source data base (data type DATETIME) when i open the csv file some show the exact time stamp and some records show just the seconds like (00:00:0.7). I used CAST CONVERT bu still the same issue.
AppliedDate
00:00.6
00:00.6
10/2/2015 0:00
10/2/2015 0:00
00:00.3
00:00.3
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
I have a table with a field "StartedAt". I wish to capture all the data in that table which has Yestarday's StartAt date.
My script below captures the data which has yestardays "StartedAt" info as well as today's date till now. How can i capture only yestardays info only.
SELECT StartedAt
FROM myTable
WHERE StartedAt >= DATEADD(day, DATEDIFF(day, 0, getdate()), -1)
please help.
i am creating a DTS package that will query a table and move data to another table daily on same DB/SERVER. i want to be able to timestamp previous date into date column in destination table. There's no date on source but i need timestamp on each import and date for previous day b/c importing data are for previous day.
View 5 Replies View RelatedI'm trying to capture the sizes of all Databases into a Permanent Table and include the Date.
It works when inserting into a #Temp Table.
When I try inserting into a permanent table it returns NULL.
The following code needs modified to create a permanent table and store the Date:
CREATE TABLE #databases ( DATABASE_NAME VARCHAR(50), DATABASE_SIZE FLOAT, Date VARCHAR(100) )
INSERT #Databases EXEC ('EXEC sp_databases');
SELECT@@SERVERNAME AS SERVER_NAME, DATABASE_NAME,
DATABASE_SIZE AS 'KB',
ROUND(DATABASE_SIZE / 1024, 2) AS 'MB',
ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS 'GB',
CONVERT(date, getdate()) AS Date FROM #databases
DROP TABLE #databases;
I'm having trouble importing data with a binary timestamp.
I have a group of datafiles with various data types mixed in
and I seem to get a lot of errors during the import attemt using
BCP.. The timestamp data is represented in the text file to be
imported as 0x0000aedc (Hexidecimal representation of timestamp)
I'm using the tilde (~) as a delimiter. What can I do to get past this
rather annoying problem?
Datetime seems to be accepted fine if I use char as the import datatype
and 26 as the length. It seems to swallow that fine. It seems to be the
timestamp field I'm tripping on.
Timestamp comes up as [binary]
Prefix length = 2
Field terminator = ~
hi, good day, i would like to convert timstamp value into date with dateformat (yyyy-MM-dd) , how to i do that ?
i have try using convert method with code 20 and 21 , but it contain the time as well , i just need the date with no time and seconds include
thank you
Good Morning,
Hate to sound so dumb, but, it's been years since I've done any SQL programming and I was bad at the time!
What is the code for taking my time stamp 2007-02-09 10:15:02.000 and just displaying the date as 02/09/2007?
Do I use variables? Are they set up before my select statement? Can anyone suggest a really good, easy, very explanable SQL (that will work in SQL Server) progamming book?
Thanks so much!
Dianne
I need to create a calculated field that will give me 'yes' or 'no' if a date field is within +/- 28 days of another date field.
So for example if a.date = 1/1/15 and b.date is 30/3/15 the calculated field will say 'no'; if the b.date was 10/1/15 it would say yes.
Similarly, if the b.date was 1/11/14 it would say 'no'; if it was 10/12/14 it would say 'yes'.
I have a requirement to track the sqlserver when any DB changed from multiuser to singleuser.
I want to capture the date and time when anyone change any db from multiuser to single user and save this information in one text or csv file.
How can we do this using powershell?
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.
I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM
Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:
1/18/2005 1:03:19 AM
get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.
Anyone know what I can do to get this sorted correctly?
I have a column where i have Timestamp column
Ex:
130670901526899350
I will convert this one into date column like
DD/MM/YYYY
or DDMMYYYY
Does anybody know of a way to set up a task which will send an email with an attachment other than using sp_sendmail. Sp_SendMail will run a query and send the results as a text file. But I need to run a stored procedure which generates an Excel spreadsheet then have an email sent with the Excel file as an attachment. The sp_sendmail text file just doesn't work with the information I am generating so I need to find another solution.
Thanks for any help.
I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:
use master;
go
CREATE PROC pr_sendmail
AS
DECLARE @DT DATETIME
SET @DT=GETDATE()
BEGIN
EXEC xp_sendmail @recipients = 'me@work.com',
@message = 'send email from SQL Server Stored Procedure.',
@copy_recipients = 'me@work.com',
@subject = 'Job Started at ', @DT
END
How do I get this to work? Thanks!
I have a set of data with epoch timestamps. Purely for the sake of reporting, I need to pull the last six months of data and group it by month. I have tried searching the googles for epoch/milliseconds to datetime but I only get the MySQL or Oracle results, not MSSQL.
View 14 Replies View RelatedWith this qry i need to only show in the result for ordate the date and not the time stamp. below is my qry and the first line of the results:
SELECT DISTINCT
adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust,
IDST_EVENT_ORDERS.ORDATE,
IDST_EVENT_ORDERS.ORTIME,
IDST_EVENT_ORDERS.EVWMIN,
IDST_EVENT_ORDERS.EVTDES,
[Code] ....
result for the field trying to change
ORDATE
1/1/2014 12:00:00 AM
I Only want to see the date.
I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROM PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
Thanks.
Tim
Hi,
I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?????
Hi,
I am moving a file to another folder after processing. I want to append the month, year and time. I have been able to append yr and month using this
(DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )
How to append the time to this?
thanks
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View RelatedI have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
As we know there are two output windows in sql server management studio like 'Results' and 'Messages', I want to capture that 'Messages' window into a log file using powershell program . like into a log file it should display like :
ex:-
(1 row(s) affected)
(1 row(s) affected)
Let me preface by saying I am not very familiar with SSIS.
Ideally, since the Transfer SQL Server Objects task can do all tables, I would like to use it to copy only data from one server to a new server that has the tables pre-created. When I encounter any kind of error, in addition to the error information provided by SSIS, I also need the actual row data.
If using the Transfer Object task can't do that, how would I loop through all the tables on an OLEDB source and capture the same error information on the destination? I figured out how to do the Data Flow a table with a redirect error output but that does not give me the actual row data.
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
Ok, I'm not quite sure how to approach this one. This is a VB.NET console app in which I want to capture each row and throw it into a table. The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.
1) What should I use to do this, dataset? I want to use stored procedures also, not inline SQL
Function here takes an incoming file, and splits it up into separate files. I want to insert each row that is succesfully split
Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)
If sIncomingfile <> "" And sOutputDirectory <> "" Then
Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None) f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read
Dim file As New IO.FileInfo(sIncomingfile) Dim filefs As IO.FileStream = Nothing If file.Exists Then Try filefs = New IO.FileStream(file.FullName, IO.FileMode.Open) 'Place: 1 Catch ex As Exception SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1") Application.Exit() End Try End If
Dim reader As New IO.StreamReader(filefs) Dim counter As Integer = 0
Dim CurrentFS As IO.FileStream Dim CurrentWriter As IO.StreamWriter Dim extension As String = IO.Path.GetExtension(file.FullName)
If extension = ".mnt" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If IsNumeric(Line.Substring(0, 1)) Then Dim Parts() As String = Line.Split(" "c) ' split row into parts If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If
If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If
End If End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "mnt")
ElseIf extension = ".naf" Then While Not reader.Peek < 0 Dim Line As String = reader.ReadLine If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file counter += 1 If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close() CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create) CurrentWriter = New IO.StreamWriter(CurrentFS) End If
If Not CurrentWriter Is Nothing Then CurrentWriter.WriteLine(Line) End If
End While
If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
MoveFilesFTP(sOutputDirectory, "naf") End If Else 'input file not valid SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1") End If End Sub
I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.
Is there any other way at all to achieve this directly from SQL Server using T-SQL?
Our database is running in full recovery mode and we backup the logfile every hour and the database file every night. I notice thetimestamp of database file *.mdf remains unchanged a few days. Forexample one database has its timestamp at Apr 15 while it is Apr 20already.I am wondering if this is acceptable. How do I force the system toflush the data and dirty blocks to the disk more frequently? What isthe downside to this?Thanks for any idea.
View 1 Replies View RelatedI have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.
So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?
Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?
Thanks,
Phil
I have 2 years worth of data that are stored in individual .dbf files for each day. Is there a way to 1 quickly import all of these tables into one and 2. move the timestamp from the file name to a date column?
Any help would be greatly appreciated
I am trying to generate a text file with current timestamp uisng SSIS. What is the best way to do this? can you show me some examples?