how to Archive the Flat files after they are loaded into a directory

I have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution

Dev2624 wrote:

I have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution

Use the FileSystem Task. You should also look into the MULTIFILE connection manager. See if these do what you want and if you have any problems, reply here.

-Jamie

|||

I am sorry if i had confused with my topic but what i want to do is once i load the Flat file into the dimension i want to archive that file in another directory with the current date time stamp. Please provide me the solution using ssis.

ex: source file abc.txt

i want to archive as abcmm/dd/yyyy.txt in archive folder say folderarchive

|||

I think Jamie pointed you in the right direction. See if this example takes you further:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

|||

Dev2624 wrote:

I am sorry if i had confused with my topic but what i want to do is once i load the Flat file into the dimension i want to archive that file in another directory with the current date time stamp. Please provide me the solution using ssis.

ex: source file abc.txt

i want to archive as abcmm/dd/yyyy.txt in archive folder say folderarchive

I perfectly understand what you want to do and I am trying to help you. What I am NOT going to do is write the solution for you.

Have you identified that the FileSystem task can be used to move files? Hopefully you have. if you want to change the name of the file when you move it then use expressions.

This should particularly help:

Setting expressions

(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/SSIS-Nugget_3A00_-Setting-expressions.aspx)

As might this:

Searching for "FileSystem" on the SSIS search macro

(http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=filesystem or http://search.live.com/results.aspx?q=file+system&form=QBRE&q1=macro%3Ajamiet.ssis)

Rafael also has a very good post on it:

File System Task Move and rename files in one step

(http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html)

-Jamie

|||

Jamie Thomson wrote:

Rafael also has a very good post on it:

File System Task Move and rename files in one step

(http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html)

As you now know seeing as he beat me to a reply

|||

Thanks for the quick responses , both the links provided me an insight i will be working on it and let both know about any further issues. Thanks for the response

|||

I was able to do that thanks for the help but i have one more thing ahead is that i have to rename the file using a value taken from the header row in the file. Do i need to use a global variable and capture header into that.

I am a newbie and your direction is encouraging i also need your guidance in checking whether a list of files exist in the directory or not how can i acheive that , i used scripting task to check for whether a single file exists but how to check for multiple files , is there a way we can configure the scripting task to check for list of files.

Please guide me

|||

If you need a data element that inside of the file; you can use an script task to get it.

There are several ways to check if a file already exists. If you already have an script task that check for 1 file; you could embed it in a ForEachLoop with a File enumerator to loop through all files in a directory.

how to Archive the Flat files after they are loaded into a directory

I have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution

Dev2624 wrote:

I have to archive the Flat files after they are loaded into an archive folder with a Date time stamp on the folder in the format mmddyyyyhhmmss what task should i use in ssis to complete this and can i do this task for multiple files in the directory, how should i configure such that all files are archived and placed in one directory with a current timestamp, Please Provide me with a solution

Use the FileSystem Task. You should also look into the MULTIFILE connection manager. See if these do what you want and if you have any problems, reply here.

-Jamie

|||

I am sorry if i had confused with my topic but what i want to do is once i load the Flat file into the dimension i want to archive that file in another directory with the current date time stamp. Please provide me the solution using ssis.

ex: source file abc.txt

i want to archive as abcmm/dd/yyyy.txt in archive folder say folderarchive

|||

I think Jamie pointed you in the right direction. See if this example takes you further:

http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

|||

Dev2624 wrote:

I am sorry if i had confused with my topic but what i want to do is once i load the Flat file into the dimension i want to archive that file in another directory with the current date time stamp. Please provide me the solution using ssis.

ex: source file abc.txt

i want to archive as abcmm/dd/yyyy.txt in archive folder say folderarchive

I perfectly understand what you want to do and I am trying to help you. What I am NOT going to do is write the solution for you.

Have you identified that the FileSystem task can be used to move files? Hopefully you have. if you want to change the name of the file when you move it then use expressions.

This should particularly help:

Setting expressions

(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/SSIS-Nugget_3A00_-Setting-expressions.aspx)

As might this:

Searching for "FileSystem" on the SSIS search macro

(http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=filesystem or http://search.live.com/results.aspx?q=file+system&form=QBRE&q1=macro%3Ajamiet.ssis)

Rafael also has a very good post on it:

File System Task Move and rename files in one step

(http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html)

-Jamie

|||

Jamie Thomson wrote:

Rafael also has a very good post on it:

File System Task Move and rename files in one step

(http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html)

As you now know seeing as he beat me to a reply

|||

Thanks for the quick responses , both the links provided me an insight i will be working on it and let both know about any further issues. Thanks for the response

|||

I was able to do that thanks for the help but i have one more thing ahead is that i have to rename the file using a value taken from the header row in the file. Do i need to use a global variable and capture header into that.

I am a newbie and your direction is encouraging i also need your guidance in checking whether a list of files exist in the directory or not how can i acheive that , i used scripting task to check for whether a single file exists but how to check for multiple files , is there a way we can configure the scripting task to check for list of files.

Please guide me

|||

If you need a data element that inside of the file; you can use an script task to get it.

There are several ways to check if a file already exists. If you already have an script task that check for 1 file; you could embed it in a ForEachLoop with a File enumerator to loop through all files in a directory.

sql

How to add date and time to Flat File Connection Manager

I have specified a name for my output file in the properties of my Flat File Connection but how can I add the date and time to that filename? If it is using expressions, what would be the expression and where exactly do I paste that expression?

Nice one:

Expression:

"FileNameBase_" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()

Looks like this:

FileNameBase_2006-01-03_08:55:28

Place is on the Connection String property of the Flat File Connection Manager.

HTH,

K

|||

I have the following expression attached to the ConnectionString property of a File Connection Manager:

@.[System::PackageName] + (DT_STR, 4, 1252) DATEPART( "yyyy", @.[System::StartTime] ) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART( "mm", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "dd", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "hh", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "mi", @.[System::StartTime] ), 2) + RIGHT("0" + (DT_STR, 4, 1252) DATEPART( "ss", @.[System::StartTime] ), 2) + ".log"

Which looks horrible, I know, but evaluates to something alot more user-friendly:

PackageName20060103084130.log

You can choose to make this as difficult or simple as you like. For example

@.[System::PackageName] + (DT_STR, 30, 1252) @.[System::StartTime]

evaluates to:

PackageName03/01/2006 08:41:30

Hope that helps!!

-Jamie

|||Thanks a lot! this gives me a real good start to working more with expressions in SSIS|||

one more question. I tried putting it at the end of my string but it doesn't like it because it is malformed:

C:\Documents and Settings\sss\Desktop\output.txt + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()

|||

Gotta escape the '\' character and gotta be in quotes... :)

"C:\\Documents and Settings\\sss\\Desktop\\output.txt" + (DT_WSTR,15)(DT_DBDATE)GETDATE() + "_" + (DT_WSTR, 15)(DT_DBTIME)GETDATE()

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing

How to achieve "On Error Resume Next" login in SSIS For Loop

Hello All,

I am developing a package using SSIS which needs to do the following.

1. Read all flat file from a folder. I am doing this using For Loop task. I know the total number of files in that folder hence I am setting the loop counter = file count.

2. The next step is to import the data from flat file to SQL server destination table using data flow task.

3. Upon successful completion of data flow task there are some other tasks like SQL to do some checks/validation on the data, export it to another tables.

Upon successful completion of step 3 the iteration goes to next file.

I want to achieve the following

IF step 2 has error (for example corrupt file or incomplete data), I want to fail data transfer completely, skip step 3, and go to step 1 for next available file and do rest.

How do I do this in SSIS?

Thanks for your help.

SGK

Hey SGK,

trying setting the maxerror count in properties to = 0

works for me, so it will keep going,

default is 1 so if an error occours it fails the whole thing