SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

benking9987
Posting Yak Master

121 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.

visakh16
Very Important crosS Applying yaK Herder

India
52309 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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000