Update 4/23/2014: I have posted a new method using a Script Task.
Had a request to dump a large dataset from a SQL query into a CSV file and then upload that file to a Sharepoint document library.
Because the resulting file was large, I zipped the file using a custom Zip Task.
To get the file from the database server’s local drive to Sharepoint I used a File System Task.
The only thing to configure is the DestinationConnection and SourceConnection. When you click on both, you will be prompted to create those File Connection Managers.
Your source is where you saved your flat file (or zip).
My destination was entered as https://mysite/subsite/Shared Documents. This, however, was translated into a nice UNC path.