SSIS Q: How to Read in newest flatfile, append it to a table, and add the date in a new column?

Question

SSIS Q: How to Read in newest flatfile, append it to a table, and add the date in a new column?

PayCheck

SSC-Addicted

Points: 427
More actions
March 25, 2009 at 3:35 pm

#219426

EDIT: This thread is evolving, I am now working to resolve other issues, however I will leave everything else here to help other newbies looking for answers to similar questions.
-- The thread is now mostly about figuring out a way to get data modified the way you want it --
So, I am making a new flatfile every night on my servers, and then I want it to get it's info appended to a table that is in my database. I would also like to be able to add a date column to my table (the flat file is generated using a powershell script I wrote, I had a much easier time learning powershell than I am this "helpful" gui system I'm working with in MS Visual Studio :O).
Right now, I can use the import wizard to take a single file, and make a table out of it, but this doesn't help me much, because I want to be able to use this table to have report services make reports out of it using queries... and I don't really know how to automate everything I want. The two biggest problems I'm having right now are how to only read in unread files (should just be the newest one, I'd really rather not delete all the old ones) and how to append the information onto an existing table. Anyway, if anyone could shed some light on this for me, I'd greatly appreciate it.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Just the Append thing: You will need a Flatfile CM (connection manager) for the source file and an OLE DB CM for the target database. Create a DataFlow Task, then in the dataflow for that task, make a Flat file Source mapped to the Flat file CM and an OLE DB destination using the OLE DB CM.

To add the datetime column you will probably want an Import Column or Derived Column transform. Then connect the source component to the transform and connect the transform to the destination component.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

PayCheck SSC-Addicted Points: 427 More actions · Answer 2

So that worked perfectly. What I have now is my powershell script just overwrites itself everyday, and then gets added to the table. I am using a derive column to add the time at which it gets added to the table.

Now my only remaining challenge is that I also want to add another column, age to the table. the age of all the entries starts at 0, and then all the entries should get updated after the new ones are added each day (effectively making todays 1, yesterdays 2, the day before that 3, etc.) Only, I don't know how to drop the old table before adding the updated ages, or how to just modify the existing column without having to drop/recreate the entire bloody thing. Also, I would really like it to only save the last 90 days worth of entries, so I would like to purge data with an age > 90. If you can tell me how to do all this I will be extreamly greatful!

Jetro Ten Centuries Points: 1257 More actions · Answer 3

Hi,

with your datetime column you already have the age. If you want to display the age in days in a new column add it as a computed column with DATEDIFF(day, YOUR_DATE_COLUMN, GETDATE()).

To delete the old data simply add a T-SQL task with a delete statement.

delete from your_table

where age > 90

or

delete from your_table

where DATEDIFF(day, YOUR_DATE_COLUMN, GETDATE()) > 90

Hope that helps.

-----------------------
SQL Server Database Copy Tool at Codeplex

PayCheck SSC-Addicted Points: 427 More actions · Answer 4

Jetro (3/26/2009)
Hi,
with your datetime column you already have the age. If you want to display the age in days in a new column add it as a computed column with DATEDIFF(day, YOUR_DATE_COLUMN, GETDATE()).

Will this update the age everyday? Also, (for learning purposes) can I make a script to do this manually and what would the code for that be if I can? (The sudo code is just [Age] = [Age] + 1, which should be the simplest thing to do ever).

Anyway, I'll see how that works for me, unfortunately, I can't really check if it auto-updates the age column within my shift today though.

EDIT: nm, I'm retarded, took me 10 min to figure out that you have to put day in ""s... lol.