July 28, 2008 at 8:35 am
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 :ermm:
July 28, 2008 at 9:42 am
The options are endless here on how to perform this and you would not even need a script task, or need to code very little in the task.
In SSIS, there is a FTP task that you can use that would handle most of the heavy lifting of what you need to accomplish. In addition, there is an Expression that you can manipulate the properties of the task to handle the dynamic portions of the file naming convention by using a variable to add to the file name.
The variable can be manipulated via a script task, which is pretty simple or since you using SQL Server, you can setup a simple table that would store the location information of where you can pick up the file, the destination location of where the file will be placed and maybe even the date to add to the file name. With that approach, you would not even need a script task, a stored procedure could return the results for you and store the values in a local variables in the package.
Unlike the DTS ActiveX scripts where you needed to code quite a bit to get the same results above, the script task is intended to help, not be the main driver in SSIS.
Work with the FTP task and see what you can accomplish.
HTH
July 28, 2008 at 11:47 am
Thanks Chuck for a breif overview of what would need to be done. Could you give me a step by step list,so I can follow through.
Is there a size limit for ftp,as currently m file size is greater than 7000kb?
How would I pass all the files in ftp,Do I need to use a foreach loop?
July 28, 2008 at 12:13 pm
as far as file size is concerned, i do not know of a limit but somebody else may be able to help answer that. i do not think you would have a problem but it may take a while to process depending on your connection.
as far as receiving the files, you can wildcard the files, something like /home/*.txt in the Remote Path will receive all of the files from the remote destination.
However, since you are renaming the file on the transfer, you may want to setup a simple config table in the database that has the information and use the for each loop to walk through those records with explicitly stating the file name to pick up and the path to send it to in the Local Path property.
you will need to create a connection manager for the remote location and a connection manager for the local storage area.
also, you will want to setup the FTP to be a receive files as the Operation property.
this should get you started, work with the task, try hard coding a couple of properties to get familiar with the task before using the Expressions properties to make it more flexible and variable.
good luck
September 25, 2008 at 12:52 pm
I got the solution here
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply