Exporting to a text file, naming the export file the date it was exported, then zip it

Question

Exporting to a text file, naming the export file the date it was exported, then zip it

Viewing 15 posts - 16 through 30 (of 30 total)

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

koln SSCrazy Points: 2189 More actions · Answer 1

Ok, I think Im there. I went with your suggestion. A couple of questions:

do I choose all default values in the Execute Process ?...do I need to put anything under 'Arguments' in the Execute Process ?..require fullfilename ?

What's next boss ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

That is one you will have to play with, just like I did with our zip utility. That is one reason I like SSIS, I can disable all the other tasks to test the one I am working on currently. Just in case, keep a copy of the file you are zipping in another directory. This gives you a quick backup if you trash the file while testing.

It's time to head home for the mid week holiday. I don't know if I will be able to assist tomorrow; but if not I will be in to work on Thursday. This actually gives me some time to look over our process a bit more.

I will suggest again that you pickup that book I mentioned earlier. It has been a big help to both me and my team mate on the projects we are currently working on with SSIS.

koln SSCrazy Points: 2189 More actions · Answer 3

ok, that sounds good...thank you so much for your time and providing me with some hope...I'll look forward to hearing from you on Thursday.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Thought I'd check and see how your zip process is coming. I will take a bit of time and work on the next two pieces of your puzzle for renaming the zip file. One thing I need to know is the name of your zip file, i.e. filename.zip or whatever zip program calls it.

Thanks

koln SSCrazy Points: 2189 More actions · Answer 5

Well, when I run the zip process task, it excecutes fine (turns green)...but the file I want zipped, doesnt get zipped...where in the zip task do I specify the location of the current .txt file ?....if we get the zip to work, the filename would be 20070703.7z

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Two things. One, I don't know your zip program so you will have to read the help files for it to figure how to use it. Second, we really have to change the name of your files. I assume you are exporting the data from a single table, tablename. I would name the output file tablename.txt and then the zip file should be named tablename.7z.

The next step in our process will then rename tablename.7z to tablename.yyyymmdd.7z. I highly recommend the yyyymmdd format as it will sort the zip files in a logical order.

With this additional info, I can make some changes to a script I have to assist in the rename and get the info to you regarding the next step in the extract process.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I did think of one thing, what did you set the working directory to in the Execute Process Task? That should be where the zip file would be created.

koln SSCrazy Points: 2189 More actions · Answer 8

The Executable property is: C\Program Files\7-Zip\7z.exe

Working diretory is: \\networkdrive\SSIS\

I understand that the working directory should be where the zip file will be created, but where do I tell the Zip procedure the source destination of the original .txt file ? (screen_touches.txt)..which Im currently exporting out of SQL server and putting in the above mentioned directory (\\networkdrive\SSIS)

and I named the output table screen_touches.txt

Thank you

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Did you find where 7-Zip created the original zip file? What is the setting for RequireFullFileName?

Checking our setup, RequireFullFileName is false, Executable is gzip.exe.

On the server, I made sure that the path environment variable for the system included the path to where gzip.exe was installed.

Our WorkingDirectory is set to \\ServerName\Export\SubDirectory and gzip sees the files in that directory and zips them in that directory. Again 7-Zip may be different in how it handles this.

koln SSCrazy Points: 2189 More actions · Answer 10

When you wrote:

"On the server, I made sure that the path environment variable for the system included the path to where gzip.exe was installed."

Where on the server did you do that ?

Also, to back up a bit...I have two variables currently set up:

1. "SourceVar" that points to the screen_touches.txt file on \\networkshare\SSIS

2. ..and "DestVar" that currently has default values in it

am I correct that these two currently do not play in role in testing the Zip process ?

I guess my main question is: Where in the Zip executable process do you call out the file that you want to zip and where do you tell it to put the file ?

Also, when I change the Executable value to just '7z.exe' (instead of C:\Program Files\7Z\..it bombs out (turns red) as it doesnt know where to find the .exe..but Im guessing that is what your describing above ?..calling out the path on the server ?

koln SSCrazy Points: 2189 More actions · Answer 11

Here are some of the messages:

Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Error: The type of the value being assigned to variable "User::SourceVar" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

I'll get back to you on the errors you are getting, I need to look at them a bit but it may be a bit before I can get back to you on those. Also, to help out there, how are the variables defined (what type are they declared)?

To setup the path, you need to go to MyComputer (on your desktop and on the server), right click and select properties, Advanced tab, environment variables. Under System Variables, there is one named Path. You need to add the path to where the system can find 7zip.exe to this variable. You need to do this on your desktop for testing, and your server for production work.

More later!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Also, I am reviewing all of our posts to make sure I know where we are, as I don't want to leave out a step.

koln SSCrazy Points: 2189 More actions · Answer 14

At this point, I think I just want to be sure that I have the zip process down..could we take baby steps and only create a zip process that zips a file ?

1. Drag and drop a Excecutable process intot the project page

2. Right-click and hit 'edit'

3. Under the process step, I enter 7z.exe for Executable (since I put the path of 'C:\Program Files\7-Zip' into the system variable on my desktop)

4. Under working directory I put the the path of where the exported file resides:

\\networkshare\SSIS (I assume that I dont call out the actual text file ?)

Now at this point, I dont know how SSIS knows to zip my screen_touches.txt file and where to put thix .zip file

Please dont worry about disecting those error messages....that might lead us down the wrong path...for now I just want to be sure that I have the zip process down.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

In our process we put *.txt in the arguments property as we have multiple files to zip. This is where you may want to put the the name of your file to be zipped. Again, with 7-Zip, I'm not sure.