Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Help with Script Task
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

124 Posts

Posted - 07/30/2013 :  11:53:04  Show Profile  Reply with Quote
I have a delimited flat file that I'm exporting using SSIS to from a SQL table to a .txt file that is pipe "|" delimited. I have been able to get the file out just fine, but I need to perform some edits to the file after it is exported. I'm thinking a script task is the best way to do this.

Specifically, I need to find/replace certain data within the resulting .txt file. I am quite familiar with SSIS/T-SQL/SQL Server generally, but I have no experience writing scripts.

As an FYI, each time I run the SSIS package, a NEW file is exported and the file name has a constant plus a timestamp. For example, the resulting file would be called: FileName_20130730095200.txt.

Anyone have any good ideas?

Thanks in advance.

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 07/31/2013 :  02:07:58  Show Profile  Reply with Quote
You dont need to do this after creating file. You can include derived column task in your data flow for doing the replacements by adding expressions based on Replace() function on the source table data. Then choose replace column option to make this column replace the actual source table column. Then map this to corresponding column of the file in file destination task and you'll get transformed data into file without any need to reopen and parse it.

As for the dynamic filename, add a SSIS variable of type string to hold file name (say FileName). Set EvaulateAsExpression property true for the variable and set expression as

"FileName_" + (DT_WSTR,30)(DT_DBDATE)@[System::StartTime]

then in Flat File destination task. go to expression and write an expression for connectionstring property to map to the above variable to get filename automatically. You might have to also append the full path of location where file needs to be saved before the above filename value

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.29 seconds. Powered By: Snitz Forums 2000