Export to Excel with Dynamic FileName

  • Hi Phil, the data source is a table from SQL server rather than from a file system. So, can I still use your script to do it? Thanks a lot!

  • ryan213 (7/16/2009)


    Hey Phil,

    Do I need to change anything in that script? I'm pretty sure I followed everything. I created a string variable called SourceFile that contains the location/filename of the original file. Everything runs properly, but I don't see the new file that contains the month in the filename.

    Thanks again!

    Ryan

    I think it should work as-is. Did the task turn green to indicate that it had run successfully? The new file's folder will be the same as the source file.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • jim (7/16/2009)


    Hi Phil, the data source is a table from SQL server rather than from a file system. So, can I still use your script to do it? Thanks a lot!

    Hi Jim

    The process I am envisaging, as I mentioned earlier in this thread, is something like this:

    1) Copy Excel template (field headings, but no data) to a standard file location/name (this will be your destination connection, as configured in SSIS)

    2) Use data flow task to output data from SQL Server to this 'standard' file

    3) Run script to copy from 'standard' file just populated to new file, based on required naming conventions.

    As it stands, the script copies c:\export\x.txt (or whatever the standard file is called) to c:\export\Junex.txt (June being 'last' month), but it would be easy to modify to suit almost any other naming requirement.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Yup, everything ran fine. Saw the box turn Green. 🙂 But the filename remains the same "Output_.xls." Do I need to do anything else in the Script Task Editor?

  • Um..so yeah...it's working...except the new file is located in the directory above, not the same directory. Hmm...

  • Interesting. Can you give me an example of what your source file variable contains?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • OK, I figured out why it was saving to the parent directory.

    FileDestination = FileIO.FileSystem.GetFileInfo(FileSource).DirectoryName & "\" & MonthName((Now().AddMonths(-1).Month)) & "_" & FileIO.FileSystem.GetFileInfo(FileSource).Name

    I added the "\" between the DirectoryName and the MonthName. But now my filename is: June_Output_.xls. Output_June.xls would be great! Or better yet (might be asking for too much), Output_June_2009.xls.

    My SourceFile variable is: \\000.00.0.000\exports\Ryan_Files\Test\Output_.xls

  • Try this:

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

    Dim FileSource As String, FileDestination As String

    Dim file As FileIO.FileSystem

    Dim Folder As String, Filename As String, FileExtension As String

    Dim LastMonth As String, ThisYear As String

    'Set the file source from the input variable

    FileSource = Dts.Variables("SourceFile").Value.ToString

    'Set the various variables

    LastMonth = MonthName((Now().AddMonths(-1).Month))

    ThisYear = Year(Now()).ToString

    Folder = FileIO.FileSystem.GetFileInfo(FileSource).DirectoryName

    Filename = System.IO.Path.GetFileNameWithoutExtension(FileSource)

    FileExtension = FileIO.FileSystem.GetFileInfo(FileSource).Extension

    'Assemble the spec of the destination file

    FileDestination = Folder & "\" & Filename & LastMonth & "_" & ThisYear & FileExtension

    'Now perform the copy

    ' CopyFile(FileSource, FileDestination)

    Dts.TaskResult = Dts.Results.Success

    End Sub

    Private Sub CopyFile(ByVal Source As String, ByVal Destination As String)

    Dim file As FileIO.FileSystem

    file.CopyFile(Source, Destination, True)

    End Sub

    End Class

    As you will see if you examine the code, I have broken out the various components and then assembled the destination file spec at the end - hopefully you will be able to use this to get the destination file name in whatever form you want.

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Freakin' genius! It worked, after I "un-commented" this line:

    ' CopyFile(FileSource, FileDestination)

    hehe

    I'm having trouble understanding how the Precedence Constraint Editor works though. I can sort of get it to work, but I don't completely understand it. I want it to create the file if there are records, but not create the file (and send me an email) if there are no records.

    I have a Row Count that saves to a variable called RowCountResult, then I have that connected to two things: Data Flow task to create my file and also an Execute SQL Task to send me an email if there are no records.

    The precedence constraint between my Row Count and Data Flow Task is set up like this:

    Evaluation operation: Expression Or Constraint

    Value: Failure

    Expression: @RowCountResult ==0

    Can you offer an explanation/solution? I know I've been asking a lot, and thanks so much for your help!!

  • Ah yes - that copy line was causing me pain because my file system does not have the path you were using, so I muted it 🙂

    Change your Evaluation: Expression and Constraint (you want both to be true, not either - this is likely to be your problem)

    Value: Success

    Change your Expression 1: @[User::RowCountResult]==0

    Same thing for your other branch - except it has

    Expression 2: @[User::RowCountResult]>0

    See how that goes.

    It's been a journey!

    Phil

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • OK, everything's working perfectly now. I just need to get Database mail to email me the file attachment. But that's for another day. hehe Thanks so much for your help! You'd get tons of rep points if I knew how to do it and if it's available! 😉

  • It's always a pleasure helping someone who's enthusiastic and eager to learn.

    No rep points here - just hoping that good SQL karma will come back to me when I get back to the UK soon & try to find somewhere/someone that will hire me:-)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I accidentally deleted a SSIS package from solution explorer. Is there anyway to find it back?

  • Eek. Stop all activity on your PC and find an undelete app from somewhere, after first checking that the DTSX file is not magically in your Recycle bin.

    You should also check the folders under the solution - maybe it's still in the BIN folder. (BIN = Binary, not 'rubbish bin'!)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply