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 |
|
benking9987
Posting Yak Master
124 Posts |
 Posted - 2013-07-30 : 11:53:04
|
| 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-31 : 02:07:58
|
| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
Help with Script Task