Script Task to validate files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Script Task to validate files

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-28 : 11:15:35

All,

I have 4 txt files coming in every Monday. These files would consist of data from the week before [Mon-Friday].
These files are zipped and have the following names.

NJCustomers07282008.zip contains NJCustomers.txt
NYCustomers07282008.zip contains NYCustomers.txt
NJClient07282008.zip contains NJClient.txt
NYClient07282008.zip contains NYClient.txt

These files are sent to our ftp .Here are the steps I need to perform.

Part 1
Copy 4 files from ftp which are of current date and have the above names to a share drive.
3 steps to be completed in share folder [M:\], unzip the 4 files, ensure the file is not empty [0 kb] and the date created is of current date.
Once all 3 steps are successful, move files from share drive to server’s local drive [E:\] into respective folders, NJCustomers, NYCustomers, NJClient, NYClient

I have created 4 separate packages, where the flat file connection mng refers to these folders for the file to process.

Part 2
Once SSIS package process is completed. It would create 4 txt files and dump them into a completed folder on the server’s local drive [M:\Completed]. The text files are as

NJCustomers.txt --> need to be zipped and renamed as NJCustomers07282008.zip
NYCustomers.txt --> need to be zipped and renamed as NYCustomers07282008.zip
NJClient.txt --> need to be zipped and renamed as NJClient07282008.zip
NYClient.txt --> need to be zipped and renamed as NYClient07282008.zip

The date padded at the end should be of the current date. Once zipping and renaming file is completed, Files will then require to be moved to Clients FTP Drive.

I have completed the SSIS packages [manipulation of data], but can’t seem to figure out how to put the head and tail to this.
I’m on my learning curve of VB.net and would be really glad is someone could give me some logic and a start up on how to perform the above. I am planning to include a script task that would perform the part 1 and send an email notification on failure otherwise go ahead and start executing the 1st package and work its way to the other remaining packages.
As for my part 2 that would be script task as well, but once the last SSIS package completes running, this script task would be executed.

Suggestion are most welcome.Im a new beginner to the use of Script task & .Net.Do help me out

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-28 : 16:21:04

All I would be greatful is someone can give me a jump start !

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-29 : 07:30:03

You can use Winzip Command Line to unzip/zip the files and invoked via Execute Process Task. You can use ForEach Loop File System to iterate through files and Script Task inside to check if filesize is not zero (empty file).

Here's the link about Winzip software I personally used. Hope this helps.
http://www.winzip.com/prodpagecl.htm

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-30 : 09:09:50

Hi There,

We have winzip command line.Any idea How I can pass the file name in the execute process task.Appreciate if you could guide me with more steps.

I have started writing the VB SCript as well for my file transfer and am stuck at the unzipping of files.Do you have any function that I can use to call Winzip command line and pass the file names that need to be unzipped.

I have this script in place,but its not unzipping the files and instead its creating .zip file.

Function WinZip(sFile,sArchiveName)
'This function executes the command line
'version of WinZip and reports whether
'the archive exists after WinZip exits.
'If it exists then it returns true. If
'not it returns an error message.

Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("Wscript.Shell")

'--------Find Working Directory-----------
aScriptFilename = Split(Wscript.ScriptFullName, "\")
sScriptFilename = aScriptFileName(Ubound(aScriptFilename))
sWorkingDirectory = Replace(Wscript.ScriptFullName, sScriptFilename, "")
'-----------------------------------------

'-------Ensure we can find WZUNZIP.exe------
If oFSO.FileExists(sWorkingDirectory & " " & "WZZIP.EXE") Then
sWinZipLocation = ""
ElseIf oFSO.FileExists("C:\program files\WinZip\WZZIP.EXE") Then
sWinZipLocation = "C:\program files\WinZip\"
Else
Zip = "Error: Couldn't find WZZIP.EXE"
Exit Function
End If
'-----------------------------------------

oShell.Run """" & sWinZipLocation & "wzzip.exe"" -ex -r -p -whs -ybc """ & _
sArchiveName & """ """ & sFile & """", 0, True

If oFSO.FileExists(sArchiveName) Then
Zip = 1
Else
Zip = "Error: Archive Creation Failed."
End If
End Function

Thank You !

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-30 : 10:54:22

Try this link to unzip the files first using Execute Process Task (scroll all the way down):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2736317&SiteID=1

Here's looping through the files and save filename to a variable.
http://www.sqlis.com/55.aspx

The file loop will basically allow you to pass the filename into the Execute Process Task parameter. You have a lot going so just wanted to breakdown in pieces. Sorry this is broad but I've done similar to yours and managed to get it working; just takes time thinking it through.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-30 : 11:16:36

Hi There,

Thank You very much foe the unzip link with execute process task.
I have added it to my favourites.

Any idea on the unzipping code in vb script as I have most part of my code already written and Im just stuck at this 1 bit.

Here is what I have corrected so far

Function WinZip(ssubFile,sArchiveName)
'This function executes the command line
'version of WinZip and reports whether
'the archive exists after WinZip exits.
'If it exists then it returns true. If
'not it returns an error message.

Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("Wscript.Shell")

'--------Find Working Directory--------
aScriptFilename = Split(Wscript.ScriptFullName, "\")
sScriptFilename = aScriptFileName(Ubound(aScriptFilename))
sWorkingDirectory = Replace(Wscript.ScriptFullName, sScriptFilename, "")
MsgBox Cstr(sScriptfilename) & ";" & CStr(sWorkingDirectory)
'--------------------------------------

'-------Ensure we can find WZUNZIP.exe------
If oFSO.FileExists(sWorkingDirectory & " " & "WZUNZIP.EXE") Then
sWinZipLocation = ""
ElseIf oFSO.FileExists("C:\program files\WinZip\WZUNZIP.EXE") Then
sWinZipLocation = "C:\program files\WinZip\"
Else
Zip = "Error: Couldn't find WZUNZIP.EXE"
Exit Function
End If
'--------------------------------------

oShell.Run """ & sWinZipLocation & """& "-d" & """ & sArchiveName & """& """ & sFile & """

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-30 : 15:53:37

Hi There,After spending more than 5 hours on my script.I decided to give your idea a try.Here is where I have got so far.Maybe you might be able to help me.

Create a foreach loop >Foreach File Enumerator
The folder refers to "E:\MKT\B1" & the Files refer to *.zip

And since I have Winzip commnad line.Can I use that instead in the executable.How will I pass the file names as variable.

quote:
Originally posted by rgombina

Try this link to unzip the files first using Execute Process Task (scroll all the way down):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2736317&SiteID=1

Here's looping through the files and save filename to a variable.
http://www.sqlis.com/55.aspx

The file loop will basically allow you to pass the filename into the Execute Process Task parameter. You have a lot going so just wanted to breakdown in pieces. Sorry this is broad but I've done similar to yours and managed to get it working; just takes time thinking it through.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-31 : 07:43:21

Okay, I think this solution is much easier to deal with.

Passing filename from Foreach Loop:
1) Create a variable (package level is fine) called "filename" as String
2) Edit Foreach Loop Container and under Variable Mappings, "Variable", select "User::filename" (dropdown)
3) To the right of that "Index" leave it as "0" (zero)

"User::filename" << this is what's going to hold the filename as it cycles through and pass it to your Execute Process Task or other usuage. Good luck!

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-31 : 14:58:59

Hi there,

Sorry for the late response.I managed to fix the unzip function using vb script with winzip command line.

Im giving the foreach loop method a try.Here is what I get when I execute USER::filename.zip is "Invalid file name".

I have added "*.zip" in the Collection Tab for file txt box.

This is the argument setting in process task -e "User::fileName"

Do let me know what Im doing wrong? Is there a way for me to watch the filename being passed from for each loop to process task [winzip]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-31 : 16:04:27

It's @[User::filename]

Use debug to see what's happening to your variables and other interesting values:
- Right-click Foreach Loop Container > Edit Breakpoints > check "... OnPostExecute" and click OK
- Run Debug and at the bottom click "Locals" tab
- Expand Variables (+ sign) and look for "User::filename"

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-07-31 : 17:06:32

Hi,

I gave the changes a try.I had included the argument as
-e -o @[User::filename]..Do I still need to put it in "".

I'm still getting the error mssg "@[User::FileName].zip.

I added the breakpoint and watched the variables in the local.The foreach loop just looped over the 1st file and passed it in the variable.After which I receive the error msg.

Do let me know what Im doing wrong.Thank You!

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-07-31 : 17:13:11

"... -e -o " + @[User::filename]

Try that.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-08-01 : 12:52:02

Hi There,

I replaced the argument as yours.Here is what I get
Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

Here all my settings
For Each Loop
-Enumerator Configuration
* Folder > "C:\A"
* Files > NULL
* Retrieve File Name > File Name & Extension
-Variable Mapping
* User::fileName
* Index =0

Execute Process Task
-Process
* Executable > C:\Program Files\WinZip\WINZIP32.EXE
* Argument > "... -e -o " + @[User::fileName]
* Working Directory > "C:\A" [Is this where the files get dumped after unzipping ?]

There are total of 6 zipped and 6 txt files in the "C:\A" folder currently.
I do not see any variable being passed in the Local Window as well.

Do guide me.Thank You !

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-08-01 : 13:11:56

Let's get this working first -

For Each Loop
-Enumerator Configuration
* Folder > "C:\A" (correct)
* Files > NULL (change to: *.zip)
* Retrieve File Name > File Name & Extension (change to: Fully qualified)
-Variable Mapping
* User::fileName (case sensitive: make sure "fileName" and not "filename")
* Index =0 (correct)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-08-01 : 14:17:35

quote:
Originally posted by rgombina

Let's get this working first -

For Each Loop
-Enumerator Configuration
* Folder > "C:\A" (correct) - [CORRECT] [Do I need to include the ""]
* Files > NULL (change to: *.zip) [CHANGED]
* Retrieve File Name > File Name & Extension (change to: Fully qualified) [CHANGED]-Variable Mapping
* User::fileName (case sensitive: make sure "fileName" and not "filename") [fileName]
* Index =0 (correct) [CORRECT]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-08-01 : 14:21:13

Once you get Foreach loop fixed, here's the Execute Process Task should be:

Process section -
RequiredFullName: True
Executable: C:\Program Files\WinZip\WINZIP32.EXE
Arguments: blank
WorkingDirectory: C:\A>> Everything else leave alone <<

Expressions section - (important)
- Click "..." button right of Expression
- Select "Arguments" under Property
- Type in: "-e -o " + @[User::fileName] under Expression
- Click OK > OK and run it
"-e -o " + @[User::filename]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-08-01 : 14:25:50

For Each Loop
-Enumerator Configuration
* Folder > "C:\A" (correct) - [CORRECT] [Do I need to include the ""]
sorry jsut C:\A

* Files > NULL (change to: *.zip) [CHANGED] (good)

* Retrieve File Name > File Name & Extension (change to: Fully qualified) [CHANGED]-Variable Mapping
select "Full qualified" under Collection > Retrieve file name

* User::fileName (case sensitive: make sure "fileName" and not "filename") [fileName] (good)
* Index =0 (correct) [CORRECT] (good)

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-08-01 : 15:20:41

You are The Man !

It works.

Could you share the zipping up arguments as well.

Thank You so much for your patience.

This created an alternative workaround for me.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-08-01 : 16:49:59

Great! Here's more detail on Winzip (pack and extract):
http://www.memecode.com/docs/winzip.html

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-08-01 : 16:57:26

I'll give it a shot for the zipping and get back to you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources