Exporting to a text file, naming the export file the date it was exported, then zip it
-
Hi folks
Any help on my request would greatly be appreciated. We are trying to maintain the growth of a particular table, by exporting and deleting data that is older then 90 days. Here are the 4 steps I need to do. I believe I know how to do 1 and 4;
1. Create a job that continously exports data that is older then 90 days to a text file
select *
from table A
WHERE (CREATED < DATEADD(DAY, - 90, GETDATE()))
2. Have the job automatically name the exported file, the day it was exported (i.e. 07032007 (todays date))
3. Then zip that file
4. Then delete the data out of the table
delete
from table A
WHERE (CREATED < DATEADD(DAY, - 90, GETDATE()))
Im not a big scripter/coder, so I was wondering if there is anything I could do in SSIS. Im more familiar with DTS, so any kind of baby steps you could provide in SSIS, would go a long way.
Thank you so much.
-
Two questions I need answered first. One, what is the format of the date in Created? Two, what software are you going to use to zip the output file?
We are exporting data daily to a MSP and we have to zip the output files prior to FTPing the files to them, so we have a procees in place to do this with SSIS.
-
Hi Lynn,
1. datetime
2. 7-zip
...thank you so much for looking into this !....
anxiously and giddy waiting..
-
Does this mean that CurrentDate is like this: 2007-07-01 13:03:01.133?
Not familiar with 7-zip, does it have a command line capability?
-
it looks like this: '3/5/2007 3:11:44 PM'
7-zip does have command line capability: 7z.exe
-
I think the easiest way to work you through this is a step at a time. I was trying to list all the steps you need to complete, but I was having a little difficulty as I got into some parts of it.
Since you have identified 4 steps, lets go with that.
Step 0, however, is this: you may also want to purchase a book, and the one I recommend is Professional SQL Server 2005 Intgration Services by Wrox. One of the authors of this book is SSC's own Brian Knight. It is an excellent reference book, as I keep a copy handy when working on SSIS packages. It doesn't answer every question, but it has helped me on several occasions.
Lets build your pack. Your first step is to extract your data, you will need a data flow task as your first task in your package. Your data source will be your SQL Database, and you should extract data using the following in your where clause for the SQL extracting the data:
created < dateadd(dd, -90, dateadd(dd,datediff(dd,0,getdate()), 0))
Note: You will want to schedule this task to run after midnight daily (at least I would)
Your output should be a flat file (probably a .csv file with column headers in the first row).
Let me know when you have this part working.
-
ok, done...instead of a .csv file, its a .txt.
What can I do next ?
-
First, quick question, what did you name the file and where are you putting it (okay, actually two questions).
-
filename: 07032007.txt
location:on my desktop
should we take this offline, thereby I dont bother everybody with my questions ?..you can email me directly by using pm.
-
I assume that you hardcoded the filename and destition. This package will eventually run on the server, correct?
-
correct
-
I'd change directions a bit. One, is there a network share on the server that you can access from your desktop and the server using a UNC (\\Servername\Directory[\SubDirectory ...]? If so, use that directory for the target directory of your file. Also, give your file a static name. We will rename name the file with a date stamp (yyyymmdd, for sorting purposes).
-
ok, done
-
You now need a Sequential Container (actually you don't but it makes things easier). Connect your Data Flow task to this container such that this container (and its contents) execute after the data Flow task.
We will also want to define a few variables, and to keep it simple do it at the package level. I'll leave the names up to you but you will variables to id source and destination directories and files. The initial values for the source variables should be your UNC directory on the server and the static name of the file. Also, put default values for the destination variables, but we will update those later in the package.
After you get that done, you will then need to drop a File System Task and an Execute Process task in the sequence container. Here you have to make a decision, do you want to rename the file itself or the zip file you put it in? I would keep the static file name, put that into a zip file (same name as the static file, but with .zip (or in our case .gz) as the extenstion), then rename the zip file with the date stamp. The choice you make determines which task you run first.
If you zip the file first, you run the Execute Process task first then the File System task. For the Execute Process task, you need to figure out how to run a command line process to zip your file. I can't help you there as I am not familiar with your software. We are using gzip in our process. I would work on this part first. You can disable both the data flow task and the file system task so that they don't run. (You may have to remove the File system task since I haven't told you what to but there yet, but hopefully disabling it will work.)
Let me know which way you decide to go.
-
Opps. Almost forgot, you will need a script task as well. This will run before the file system task.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply