Processing Files With Timestamp

May 8, 2007

hi guys,

i have a unique problem here.i get four different type of flat files,which i need to pick up and process parallelly,this i am doing using four different foreach containers for the four load process as i can get multiple files ( i had other issues like rollback etc for using forecah containers).

my problem is,my input file names will have format like this
yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt



here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.



thanks in advance

srikanth

View 1 Replies


ADVERTISEMENT

For Loop - Iterate From Older Files To Newer Files Based On File's Timestamp

Mar 13, 2008

In the For Loop, How to Iterate from Older flat files to Newer flat files based on File's Timestamp. If there are some older files in that folder, it should be processed first and then continue with the newer one.

Any Suggestions?

View 3 Replies View Related

Incrementally Processing New Files

Sep 21, 2006

Hi,

I'm just starting to learn SSIS and would like some advice on how to handle something I encounter frequently. I often have to connect to a remote FTP site which contains a large number of files. Each day a number of files are added (old files are not purged). So each day I need to download all files which have been added since last time I checked, store on a file server, then load to a SQL database.

I've written a DataFlow using a script which generates a list of files and dumps to a raw file and doing the same for FTP directory listing wouldn't be too hard - I could then feed these into a third DataFlow to work out what the new files were, then a third to download the files to a temp directory etc.

But is there a cleaner way of doing this (I'm not adverse to scripting or even writing my own components - but for maintainability reasons I'd like to keep this as "out of box" as possible)? How are other people approaching this task?

Dave

View 7 Replies View Related

Log Shipping Transaction Log Backup Files Have Wrong Timestamp

Jun 23, 2007

I'm experiencing a weird problem with log shipping in SQL 2005.

I've setup Log Shipping for a production database between two sites. The standby database is being updated correctly and everything seems to be working as expected but for one detail: the name of the transaction log backups are generated with an UTC timestamp instead of my local timezone.

The the data below extracted from the backup history:

2007-06-23 17:30:00.000 D:BackupDatabasesmydbmydb_20070623073000.trn
2007-06-23 17:15:00.000 D:BackupDatabasesmydbmydb_20070623071500.trn
2007-06-23 17:00:00.000 D:BackupDatabasesmydbmydb_20070623070000.trn
2007-06-23 16:45:00.000 D:BackupDatabasesmydbmydb_20070623064500.trn

My timezone here is GMT+10.

Although it's not affecting Log Shipping, it's very confusing as the full backups have a timestamp in the local timezone!

Has anyone seen experienced something similar to this? Please see below my SQL details:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3042.00
3 Language 1033 English (United States)
4 Platform NULL NT AMD64
5 Comments NULL NT AMD64
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT - 64 Bit
8 FileVersion NULL 2005.090.3042.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 199360512 NULL
15 WindowsVersion 248381957 5.2 (3790)
16 ProcessorCount 4 4
17 ProcessorActiveMask 4 f
18 ProcessorType 8664 NULL
19 PhysicalMemory 4095 4095 (4294037504)
20 Product ID NULL NULL

Thanks,
André

View 3 Replies View Related

Processing Multiple Unzipped Files

Feb 26, 2008

The requirement is to process two zip files in turn.

Each zip will contain either 1 or 2 csv files, but both zips will contain the same number of csv files. The structure of the csvs inside the zips is identical.

I have set up a Foreach Loop container with a File Enumerator to look for *.zip files. The zip file name is successfully retrieved into my 'Zipfile' user variable, and I use this in an Execute Process task to unzip the zip file. So far so good.

However what I now need is to get hold of the name(s) of the csv file(s) that have been unzipped, and use those in my flat file connection strings.

I considered trying to use a nested foreach loop, the outer loop enumerating the zips, and the inner enumerating the csvs. However this is no good because if 2 csvs are present, I need them both available in the control flow at the same time as they need to be merged together.

I'm sure there's a way to do this in SSIS but I'm struggling to identify it. Anyone got any ideas at all?

Thanks guys

KO

View 2 Replies View Related

Processing CSV Flat Files With Records Of Number Of Fields

Mar 10, 2008

Hello I have some flat files that contain CSV records with different number of fields but the first 4 fields of each record type are the same of each re. eg there would be an entry of one record that has eight fields and another that has 6 fields. Which of the items in the toolbox can i use to filter the records based on the entry in the first 4 fields so i can process the filtered records.

Thank you
Kenalex

View 5 Replies View Related

Processing Last Created/modified Files From A Location Using SSIS Package

Mar 12, 2008

We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.

Kindly give respond to this if any one has worked on this.

Regards,
Sajesh

View 7 Replies View Related

Error While Converting Oracle Timestamp To Sql Server Timestamp (datetime) - Invalid Date Format

Jun 19, 2007

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.

View 6 Replies View Related

Transact SQL :: Query To Display Avg Values For Each Timestamp And Count Of Timestamp

Jun 23, 2015

date        time         s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700

I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp

Output
date        time         s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400                  3
6/9/2015 11:56:26 TestSite 900                   2
6/12/2015 12:56:26 TestSite 700                   1

View 5 Replies View Related

Get Row Timestamp With No Timestamp Column

Jul 10, 2007

For starters, please feel free to move this if it is in the wrong forum.



The issue I have is this. I have been asked to delete all information from a table that was inserted before May 12 this year. The issue is that when the DB was created, whoever designedd it neglected to add a timestamp column for the user data table (the one I need to purge). Does SQL, by default, happen to store insert times? Would it be something that might hide ina log file somewhere?

View 4 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

Script Task: How To Compare Files On FTP With Existing Files In Local Folder Before Transfer!

Apr 24, 2008

In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.

I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.

Thanks for the help in advance and i apologize for the long lines of code!

example for step 1:
----------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class

example for step 2:
-------------------------------------------------------------------------------------------------------

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()

Try

'Create the connection to the ftp server

Dim cm As ConnectionManager = Dts.Connections.Add("FTP")

'Set the properties like username & password

cm.Properties("ServerName").SetValue(cm, "ftp.name.com")

cm.Properties("ServerUserName").SetValue(cm, "username")

cm.Properties("ServerPassword").SetValue(cm, "password")

cm.Properties("ServerPort").SetValue(cm, "21")

cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout

cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb

cm.Properties("Retries").SetValue(cm, "1")

'create the FTP object that sends the files and pass it the connection created above.

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the ftp server

ftp.Connect()

'ftp.SetWorkingDirectory("..")

ftp.SetWorkingDirectory("directoryname")

Dim folderNames() As String

Dim fileNames() As String

ftp.GetListing(folderNames, fileNames)

Dim maxname As String = ""

For Each filename As String In fileNames

' whatever operation you need to do to find the correct file...

Next

Dim files(0) As String

files(0) = maxname

ftp.ReceiveFiles(files, "C: emp", True, True)

' Close the ftp connection

ftp.Close()





'Set the filename you retreive for use in data flow

Dts.Variables.Item("FILENAME").Value = maxname

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View 16 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

Feb 24, 2015

I have the need to delete old backup files via TSQL job. Found this solution online:

PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD

It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?

View 6 Replies View Related

Timestamp

Apr 2, 2007

how to insert a value into table where the fieldname uses a datatype timestamp.(asp.net2.0)

View 1 Replies View Related

Db Timestamp

Jun 6, 2007

I have a timestamp in a date field of a table I have but when run it does not enter the date automatically and all I get is system.byte[] display where the dtae and time should be, how can i get it so this field places the date in automatically.???

View 1 Replies View Related

Timestamp

Jun 20, 2007

I am retrieving data from a database, and have been noticing some really strange timestamps.  Has anyone ever seen a timestamp that looks like this: 16777215Karls 

View 1 Replies View Related

SQL TimeStamp Help

Jul 12, 2004

Hello,

I just started using SQL and i don't really know too much syntax.

I have an ASP page that is inserting data into a table called YellowAlerts.
The first field in this table is AlertDate. I thought I read somewhere that there is a timestamp feature. If anyone could help me out that would be great.

Thanks.

View 2 Replies View Related

TIMESTAMP --- HELP

Jul 6, 2000

How can I add a timestamp column to a table that will let me know when
a record was inserted? (perhaps by setting a default to that column?)

For some reason, @@currenttimestamp gives me
some binary garbage.

Please help with any suggestions.

View 3 Replies View Related

TimeStamp

Aug 23, 1999

How do I "disable" the timestamp so that when I copy my data into a new
database it will use the timestamp from the last time the table was effected?

Thanks,

Tracy

View 1 Replies View Related

Timestamp

Jun 2, 1999

Hi All,

I believe that all created tables have got by default a timestamp field.
I tried to find out how to read this field with a Transact-SQL statement.
And so I don't know the syntax, is anyone can help me ?

Thanks,

Herve

View 2 Replies View Related

Timestamp

Feb 10, 2005

If i have a table with a 3 fields and one of them has a timestamp as a field type and I want to do an insert or update what is the value I am inserting for the column timestamp.

Help appreciated

View 5 Replies View Related

Timestamp

Sep 23, 2006

hello I put a time stamp in one of my column and it says binary data in the field. How do i make that binary data turn into a real date and time. If this is possible how do I separate the date from the time in the results or would I have to do this myself. Also, I need to sort the results by date can this be done?

Sorry for the stupid questions my sql book (sql demystified) does not elaborate. Thanks

View 2 Replies View Related

About The Timestamp

Sep 4, 2006

hello,
m new to sql server.can any one tell me is it possible to insert only time value in timestamp datatype.
i have table which have datatype datetime.and i have to insert only time.i can't change the datatype datetime to any other one.p
plz any one can suggest me the solution..

Regards,
Sonal.

View 8 Replies View Related

TimeStamp

Feb 12, 2008

Hello I'm not a SQL Expert but i'm using it for a few months. Now i need to use a variable that everytime that is called to a select into it gives the timestamp (or Stamp) to put on a specific field. The problem is that i need this variable not in bd conotation (like 2007-02-12 12:00:44:33) but like this 2007021212004433. Can someone help me? Thanks in advance.

View 5 Replies View Related

TImeStamp Or Not

Jul 20, 2005

Is there any reason to have a TimeStamp column onto a table that has aPrimaryKey when using SQL Server and an Access front end?I read that doing so will always eliminate that write conflict error.But I notice that when pulling a recordset of 10K records, it takes 3Xless time if I delete the timestamp column on the table before runningthe SP.lq

View 2 Replies View Related

Timestamp

Jul 20, 2005

Is there any chance to get the content of a timestamp field?I am trying to get the content via Visual Objects and I am receiving onlynothing.Not NIL but "".Is it possible to get any useful information out of this field in anylanguage?(btw I know that the MS-Timestamp is not a timeformat)THXJens

View 1 Replies View Related

TimeStamp

Jul 20, 2005

Is there any reason at all to use a timestamp column in a table havinga primarykey column???lq

View 2 Replies View Related

Timestamp

Nov 29, 2007

Hi there,



When inserting into a table with a timestamp column without explicitly setting the column_list you get the following.

ie.



insert into Table_A

select *

from Table_B



Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.



I was wondering if there's a way to overcome this without specifying the column list?



Thanks.

View 3 Replies View Related

Timestamp Database

Dec 10, 2007

HI every one
I have a timestamp column in my database. I want to use this column to get current changes but i am not able to get desired results when i use to compare that field in C#.
 Thanks in advance
 Take Care
ALLAH HAFIZ

View 1 Replies View Related

SQLDataSource And Timestamp

Apr 29, 2008

Hi folks, I am binding a gridview to a SQLDataSource that contains a timestamp column. My SQLDatasource contains an update method (via a stored procedure) that takes all of the data fields from the grid as input parameters.  I am getting an error stating 'sql_variant is incompatible with timestamp' .  The parameter definition for the update method specifies the timestamp column as type 'Object'.         <UpdateParameters>             <asp:Parameter Name="timestamp" Type="Object" />            ...        </UpdateParameters> When the data is initially retrieved, the timestamp column is converted to a System.Byte[] however I cannot specify System.Byte[] in the parameter definition (different error) I have read other post with this issue but none seemed to have been solved (while still using SQLDataSource and binding).I have tried other datatypes and they do not work as well. Any help would be greatly appreciatedThanksTom   

View 3 Replies View Related

Using Timestamp With ASP.net / SQL Server

Sep 10, 2004

I have a database that I don't want to lock. I decided that before any updates can occur I would check a timestamp value and ensure that nobody else updated before I did (avoiding the 'last update wins' scenario).

I have a problem, I can read the Timestamp from the db when I read the record. I currently use the data to pre-fill a form (gee go figure ;) ) and the user changes some values and updates.

I don't know what to DO with the timestamp value while I am holding it. I have tried putting it into a hidden field on the form but the value does not seem to translate back and forth.

Do I have to store it as in memory as part of the session or can I somehow convert to and from text??

I have about 0 experience working with byte arrays so the best answer is one with an example.

Thanks.

View 1 Replies View Related

Timestamp When Updating

Oct 4, 2005

Hi world,I would like to know how to maintain a table with a "timestamp" column. I mean a column that automatically set the current datetime when the row is updated.I don't want to go trought my application and set that in the update or insert command. I would like to know if there is a possibility of putting a trigger or something.thx

View 6 Replies View Related

Binary Vs. TimeStamp

Jan 12, 2002

Can I use "timestamp" datatype in SQL Server as the equivalent datatype for "binary" in ACCESS 2000?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved