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.
| Author |
Topic |
|
Nishi
Starting Member
1 Post |
 Posted - 2011-07-14 : 16:32:21
|
| I have designed ssis package which includes Data flow task, Script task,send mail task and oledb source, flat file destination,row count.My business requirement is to enter filename and total rows in that file.i have used script task where i have declare the variable filname and total rows and written a query in C# lagnuage.It worked well and got the rquired output but later i realised i have to put filename which can be changed dynamically with current date,time, so i put the expression in flat file connection manger and it also executed well but script task failed in giving the right output.C# code:string StrFileName = string.Empty;//int TotalRows= 0;StrFileName = Dts.Variables["FileName"].Value.ToString();if (System.IO.File.Exists(StrFileName)){System.IO.StreamWriter objWriter = new System.IO.StreamWriter(StrFileName, true);objWriter.Write(Dts.Variables["FileName"].Value.ToString() + " " + Dts.Variables["TotalRows"].Value.ToString());objWriter.Close();Dts.TaskResult = (int)ScriptResults.Success;}else{Dts.TaskResult = (int)ScriptResults.Failure;}Expression in Flat Fileconnection manager:@[User::FileName]+" "+(DT_WSTR,4)DATEPART("MONTH",Getdate())+(DT_WSTR,4)DATEPART("DAY",Getdate())+ RIGHT("0" +(DT_WSTR,4)DATEPART("YEAR",Getdate()),2)+(DT_WSTR,2)DATEPART("HOUR",Getdate()) +(DT_WSTR,2)DATEPART("MINUTE",Getdate()) +(DT_WSTR,2)DATEPART("SECOND",Getdate())Error:Error: 0x8 at Get FIlename & Total Rows: The script returned a failure result.Task failed: Get FIlename & Total RowsDo i have to write this expression in script task?Because when i removed the expression from flatfile connection manger,it successfully worked but it does not change the filename,date, time dynamically.Can anyone guide me how to add that expression in my above script???.Its in C#Your suggestion and help will be highly appreciated.Nishi |
|
|
lightsql
Starting Member
17 Posts |
Posted - 2011-07-20 : 04:32:25
|
| Hi,Why not use the script task to create the flat file as well? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-07-20 : 07:47:10
|
| i think you can achieve this without using script task by just using a Data Flow Task with flat file destination and making filename dynamic by means of dynamic properties tab expression editor------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Script task Error,Can anyone guide.