A folder having number of csv files . the files are different formats like one file as shown below id ,name ---------- 1,a 2,b some files having this formats id,name,address --------------------- 1,a,usa 2,b,uk but this files arranged two folders like Folder1 having the files like schema id,name Folder2 having files schema like id,name,address what i can do this formats ..psl tel me..

Follow this where the author computes a MD5 hash per file to determine whether the file is of a new format and accordingly takes different action on each file format.http://btsbee.wordpress.com/

A folder having number of csv files . the files are different formats like one file as shown below id ,name ---------- 1,a 2,b some files having this formats id,name,address --------------------- 1,a,usa 2,b,uk but this files arranged two folders like Folder1 having the files like schema id,name Folder2 having files schema like id,name,address what i can do this formats ..psl tel me.. You would have to read all the CSV files one by one to check what the file type is and move them to appropriate folder. To do this: 1 - Add a Foreach loop container and set it to get the list of files within the folder. 2 - With Foreach loop container Add a Script task where you would write the code to detect the file types. 3 - Next to Script task add 2 File system task and attach script task to both of them. You would configure the Precedence conditions to execute one of the 2 File system tasks. 4 - Now, Add the following code to Script Task. for this code to work you would pass the @FilePath as readonly variable and @FileType variable as read/write variable: public void Main() { // TODO: Add your code here string filePath = (string)Dts.Variables["FilePath"].Value; string[] lines = System.IO.File.ReadAllLines(filePath); int columnCount = 0; if (lines.Length > 0) { var regex = new System.Text.RegularExpressions.Regex("(([^,])+)"); columnCount = regex.Matches(lines[0]).Count; Dts.Variables["FileType"].Value = (columnCount == 2) ? 1 : 2; } Dts.TaskResult = (int)ScriptResults.Success; } 5 - When the script task executes, it will set @FileType value as 1 or 2 based on the file type. 6 - Set precedence conditions for the flow from Script Task to File System Task such as: @FileType == 1 for files with 2 columns @FileType == 2 for files with 3 columns 7 - Configure File System Tasks to copy the files to required destination folder. Hope this helps- Please mark the post as answered if it answers your question

please explain a little bit clearer about your requirements so we can help

Hello, Can you please elaborate more on what are your requirements so that we can help you better.

Based on the little i understand from your query, it seems that you have a folder with csv files of different formats and you would like them to be put into 2 different folder destinations based on their format. If yes, this looks less of an ETL task. However if you have to do it via SSIS then use a script task to parse and identify the file format and push it to the destination folder of your choice.http://btsbee.wordpress.com/

thanks for giving me reply this answer and what i am expecting actually i am not good in script thats why i am looking for how to parse the columns in script. before parsing i wrote some simple script task to identify the file is exist or not if exist data is there or not but i dont know deeply in script to check the columns in csv files(or flat files) can u please help me to reply with code or suitable tutorial for to how to parse and identify the formats/columns and how to push to destinations. and once again thanks for giving quick reply and we hope to expect quickly from u

we have a folder with csv files of different formats and we would like them to be put into 2 different folder destinations based on their format.

we have a folder with number of csv files of different formats and we would like them to be put into 2 different folder destinations based on their format.

Follow this where the author computes a MD5 hash per file to determine whether the file is of a new format and accordingly takes different action on each file format.http://btsbee.wordpress.com/

thanks for sending sir. i complete all the steps except precedence constraint when i setting the expression in precedence constraint, when i executing the package it can raise an error that was Package Validation error additional information error at foreach loop container: the expression "@FileType == 2" must evaluate to true of false.change the expression to evaluate to a boolean value. and actually this is the main requirement sir I have One Folder Named ABC in that i have Five Sub Folders (A1 to A5). in that sub folders 10 csv Files with two different Format Format 1 (Did,Eid,Dname) are the columns Format 2 (Dname,Did,Eid,Others) are the columns what my requirement is step 1: loop the files from sub folders (i completed this) step 2: check the column names step 3: move the files into destinations based on formats( csv file have did,eid,dname move the file into Format 1 folder in abcidentity(main folder) else move the file (having dname,did,eid,others) into format 2 folder) this step is also i know how to create by using file system task what my problem is how to check scan the columns and give the destinations in script task(step 2) in script task i already wrote if the file is there or not. if file is there content is there or not i dont know how to scan columns in script task if anybody can send the code. more helpfull for me Sandeep Kumar

thanks for sending sir. i complete all the steps except precedence constraint when i setting the expression in precedence constraint, when i executing the package it can raise an error that was Package Validation error additional information error at foreach loop container: the expression "@FileType == 2" must evaluate to true of false.change the expression to evaluate to a boolean value. and actually this is the main requirement sir I have One Folder Named ABC in that i have Five Sub Folders (A1 to A5). in that sub folders 10 csv Files with two different Format Format 1 (Did,Eid,Dname) are the columns Format 2 (Dname,Did,Eid,Others) are the columns what my requirement is step 1: loop the files from sub folders (i completed this) step 2: check the column names step 3: move the files into destinations based on formats( csv file have did,eid,dname move the file into Format 1 folder in abcidentity(main folder) else move the file (having dname,did,eid,others) into format 2 folder) this step is also i know how to create by using file system task what my problem is how to check scan the columns and give the destinations in script task(step 2) in script task i already wrote if the file is there or not. if file is there content is there or not i dont know how to scan columns in script task if anybody can send the code. more helpfull for me Sandeep Kumar For your error: Make sure your variable Type is Int. It should work and if it still does not then you can do something like: (DT_I4)@[User::FileType] == 2 For the column names: within the script task there is a line that you have already implemented: columnCount = regex.Matches(lines[0]).Count; here "regex.Matches" collection contains the column names that you require (with expected spaces around it which you may need to trim) which we are counting. Please mark the post as answered if it answers your question

A folder having number of csv files . the files are different formats like one file as shown below id ,name ---------- 1,a 2,b some files having this formats id,name,address --------------------- 1,a,usa 2,b,uk but this files arranged two folders like Folder1 having the files like schema id,name Folder2 having files schema like id,name,address what i can do this formats ..psl tel me.. You would have to read all the CSV files one by one to check what the file type is and move them to appropriate folder. To do this: 1 - Add a Foreach loop container and set it to get the list of files within the folder. 2 - With Foreach loop container Add a Script task where you would write the code to detect the file types. 3 - Next to Script task add 2 File system task and attach script task to both of them. You would configure the Precedence conditions to execute one of the 2 File system tasks. 4 - Now, Add the following code to Script Task. for this code to work you would pass the @FilePath as readonly variable and @FileType variable as read/write variable: public void Main() { // TODO: Add your code here string filePath = (string)Dts.Variables["FilePath"].Value; string[] lines = System.IO.File.ReadAllLines(filePath); int columnCount = 0; if (lines.Length > 0) { var regex = new System.Text.RegularExpressions.Regex("(([^,])+)"); columnCount = regex.Matches(lines[0]).Count; Dts.Variables["FileType"].Value = (columnCount == 2) ? 1 : 2; } Dts.TaskResult = (int)ScriptResults.Success; } 5 - When the script task executes, it will set @FileType value as 1 or 2 based on the file type. 6 - Set precedence conditions for the flow from Script Task to File System Task such as: @FileType == 1 for files with 2 columns @FileType == 2 for files with 3 columns 7 - Configure File System Tasks to copy the files to required destination folder. Hope this helps- Please mark the post as answered if it answers your question