Hi- I have some flat files with datetime stamp included in the header as below: "XXXXXXXX" "YYYYYYY" "Query Name:XXXXX" "Wed Nov 10, 2010 3:00 PM" I'm creating SSIS package to transfer data from multiple flat files to a destination table. I will have to include this date in the destination table to record the datetime stamp. Wondering how can this be achieved? Thanks!

could you paste more rows from your source here? how values put there? they are separated by rows or columns?http://www.rad.pasfu.com

"XXXX" "YYYYY" "Query Name: RRRRRR" "Wed Nov 10, 2010 3:00 PM" "NURSING UNIT","ROOM BED","PATIENT NAME","ACCT #","LOS","SERV","FC","AGE","____","RELIGION","ADMIT PHYS","AUTH#","REF#","INS PLAN" "BBR2","AAA","BBBBB","0000000","5","MEDI","MCR","00","F","CCCC","AAAA","N/R","","000000" "BBR2","BBB","VVVVVVV","0000000","2","MEDI","MCM","00","F","EEEE","SSSSS","000000","NR","00000" and so on I'm able to send the data in all the columns into the destination table successfully. However, I'm not sure how to populate the field in the table that needs the date from this flat file.

do you need JUST the forth row and fetch only date time from there? if yes, you can use script task to do this, you can use sreamReader .net object to read data from forth row and convert it to Datetime, then write this value in a variable. and use this variable in your data flow task. does it make sense to you? let me know where you need more detailshttp://www.rad.pasfu.com

Hi Reza- Yes, I need only date and time from the header. I've very limited knowledge in .net programming. Wonder if you could help me with the script? Thanks for your help.

OK, first create a package scope variable of datatype DateTime, and set name as DateOfFile then add an script task in control flow in the script task editor, set language as vb.net set ReadWriteVariables as User::DateOfFile then edit script, write this code instead of main() method: Public Sub Main() Dim file As New System.IO.StreamReader("D:\SSIS\flatfile.txt") file.ReadLine() file.ReadLine() file.ReadLine() Dim DateString As String DateString = file.ReadLine().Trim(Convert.ToChar("""")) Dts.Variables("User::DateOfFile").Value = DateTime.Parse(DateString) file.Close() Dts.TaskResult = ScriptResults.Success End Sub http://www.rad.pasfu.com

Thanks but I get the following error: Error: Error 30451: Name 'ScriptResults' is not declared. Line 30 Columns 26-38 Line Text: Dts.TaskResult = ScriptResults.Success

what is your SSIS version? is it 2005? the code I suggested is in 2008http://www.rad.pasfu.com

Yes. It is 2005. I replaced dts.scriptresults with Dts.Results. So the above error is gone. Thanks a lot. But I have another question: As I mentioned before I have multiple flat files..so I use foreach container to trnsfer data from each of these files..With script task outside of the foreach container with single file name listed in the script. It works fine. i.e. Dim file As New System.IO.StreamReader("C:\TestHourlyCensus\mmc_census_000238.txt") however, if I put the variable that I created for the file name in the script as below, an error occurs..Error: The script threw an exception: The path is not of a legal form. Dim file As New System.IO.StreamReader("User::varFileName") Any thoughts?

You should put this script task inside the foreach loop right before the data flow task and change your script as below: Public Sub Main() Dim file As New System.IO.StreamReader("D:\SSIS\flatfile.txt") file.ReadLine() file.ReadLine() file.ReadLine() Dim DateString As String DateString = file.ReadLine().Trim(Convert.ToChar("""")) file.Close() Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("DateOfFile", vars) vars(0).Value = DateTime.Parse(DateString) vars.Unlock() Dts.TaskResult = Dts.Results.Success End Sub unfortunately I haven't SSIS 2005 on my machine and I posted this script without test, It may cause error, please let me know if this cause errorhttp://www.rad.pasfu.com

If I use the above script inside the foreach before the dataflow..i get the following error: Error: A deadlock was detected while trying to lock variables "User::DateOfFile" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out. I also have another question: if I use a prticular filename in the script? file As New System.IO.StreamReader("C:\TestHourlyCensus\mmc_census_000238.txt") in the above code..how will other flat files go through? Dont we have to use the variable for file name?

remove the User::DateOfFile from the scrip task editor, ReadWriteVariables property and try again. and for second question, yes you can use a variable which contains file full path instead of direct path value in the script.http://www.rad.pasfu.com

Basically if I use your intial script inside foreach loop (also mentioned below)...no errors are caused but the date field will be populated with date from the first text file mentioned in the script. We will have to use a variable that takes each flat file in each iteration. I'm not sure how to do this. appreciate your help. Dim file As New System.IO.StreamReader("C:\TestHourlyCensus\mmc_census_000238.txt") file.ReadLine() file.ReadLine() file.ReadLine() Dim DateString As String DateString = file.ReadLine().Trim(Convert.ToChar("""")) Dts.Variables("User::DateOfFile").Value = DateTime.Parse(DateString) file.Close() Dts.TaskResult = Dts.Results.Success

you should add a portion for file path as variable, for example suppose that you have FilePath variable of string type which has path of file inside ,you will fill value of this variable in the foreach loop variable mapping tab ( I think you know how to do it actually ) and then go to script task editor, and set ReadOnlyVariables with User::FilePath variable then change script as this : Dim file As New System.IO.StreamReader(Dts.Variables("User::FilePath").Value.ToString()) file.ReadLine() file.ReadLine() file.ReadLine() Dim DateString As String DateString = file.ReadLine().Trim(Convert.ToChar("""")) Dts.Variables("User::DateOfFile").Value = DateTime.Parse(DateString) file.Close() Dts.TaskResult = Dts.Results.Successhttp://www.rad.pasfu.com

Thanks. I removed the User::DateOfFile from the scrip task editor, ReadWriteVariables property. Now the I dont see the error. Package runs fine but the date field in the table for all the files is geting populated with the date from the first text file! how can I fix this?

Thanks. I removed the User::DateOfFile from the scrip task editor, ReadWriteVariables property. Now the I dont see the error. Package runs fine but the date field in the table for all the files is geting populated with the date from the first text file! how can I fix this? look at my previous posthttp://www.rad.pasfu.com

Yes I made changes to the script accordingly...now i get the error: Error: The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. Any suggestions? Public Sub Main() Dim file As New System.IO.StreamReader(Dts.Variables("User::varFileName").Value.ToString()) file.ReadLine() file.ReadLine() file.ReadLine() Dim DateString As String DateString = file.ReadLine().Trim(Convert.ToChar( """")) Dts.Variables( "User::DateOfFile").Value = DateTime.Parse(DateString) file.Close() Dts.TaskResult = Dts.Results.Success End Sub

did you set this variable as ReadOnlyVariables: User::varFileName and set this variable as ReadWriteVariables: User::DateOfFile http://www.rad.pasfu.com

I removed the ReadWriteVariables: User::DateOfFile due to the locking error I mentioned in previous posts. I've now included that again. The script works perfect. Thanks a ton for all your help. Appreciate it. Thanks again, Sudha

Hi, Can you help me pleas, 1.) i nead a prorgamme in ordre to get number columns of mi file CSV. thinks. thats mi programme: Dim Msg_Error As String = String.Empty Dim Chemin As String = CType(Dts.Variables("v_Dossier_FIC").Value, String) Using reader As New StreamReader(Chemin & "\\ext_ref_hab.csv")

Hi, Can you help me pleas, 1.) i nead a prorgamme in ordre to get number columns of mi file CSV. thinks. thats mi programme: Dim Msg_Error As String = String .Empty Dim Chemin As String = CType (Dts.Variables( "v_Dossier_FIC" ).Value, String ) Using reader As New StreamReader(Chemin & "\\ext_ref_hab.csv" ) I will do of course, but It's better to create new question for this, as these pages are viewable in google it will be better if only one issue considered on each question. http://www.rad.pasfu.com

excuse me, i create new question: http://social.msdn.microsoft.com/Forums/fr-FR/sqlserverfr/thread/41980750-529c-4fb7-851a-823f5d666aaa thinks for your help