Export to Excel with Dynamic FileName

  • I would like to output to an Excel file that includes the date in the filename, such as: Monthly_Output_20090624.xls I've created two variables:

    jetengine: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Monthly_Output_

    jetengine2: .xls;Extended Properties="Excel 8.0;HDR=YES";

    And my Excel Connection Manager's Connection String expression is this:

    @jetengine+RIGHT("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + @jetengine2

    This works fine after the first run of the package, but when I try to run it the second time, I noticed that the Excel Connection Manager's Excel File Path is now referencing the newly created Excel file (with the datestamp).

    My question is will this package execute properly the next time I run it? The connection string won't be able to find the file because the date expression will have changed to the new date.

    Just wondering if it's just easier to create the regular file and then create a File System Task to copy/rename that file into the location that I want...but then, I'm not sure how to create the file system task that will copy/rename the file. hehe

    Any help is greatly appreciated!

  • I'm a little fuzzy on what you are trying to accomplish. Let me see if I understand..

    You have an SSIS package that outputs data to an Excel Spreadsheet with the name date/time based and you are using an expression to build the name.

    Questions I have:

    1. Is the spreadsheet being created elsewhere and the package is guessing the name?

    2. What is your question about subsequent runs, the express would be calculated at each run and a new filename potentially generated.

    I seem to remember something about the Excel file must exist already to output to it, but I'm not sure about that. It has been a while.. If that is true then using a script command to build the copy command would be the easiest..

  • This is how I would do it.

    1) Copy from a blank (except for column names) Excel spreadsheet file to (say) export.xls

    2) Your package always exports to this new export.xls

    3) A final File System task copies or renames export.xls to whatever file name you want.

    You should find that the File System task is pretty intuitive to use - give it a go and get back with any specific questions.

    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 have to admit, I like Phil's process..

    CEWII

  • Sure, it's easy once you put it that way... 😉 Sorry for the late reply, I've been on vacation. hehe

    I'll give it a try and let you know! Thanks again!

  • OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.

    My package creates a file: C:\Ryan\Output_.xls

    Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls

    Thanks for all the help so far!

  • Phil's suggestions is simplest. And when dealing with Excel I have found the simpler the better. The only step I would add would be to delete any files called export.xls (or whatever you decide to name your temp file) and then continue with the extraction process.

    J.D.

  • I want to use an SSIS package that outputs data to an Excel Spreadsheet (from SQL table) with the name date/time such as "output_07152009.xls". Does anyone have a detail-step info or link? Thanks for help!

  • OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.

    My package creates a file: C:\Ryan\Output_.xls

    Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls

    Thanks for all the help so far!

    Ryan , little confused here!!!!!. At the very first post u say u want datetime part in filename and now here above you say , you want (PreviousMonth'sName).xls here are the steps that will rename (move and copy file) or copy file to different location using FIle System Task.

    1. Create two variables of type string and assign value accordingly.

    "FullSourcePath" which will have value "C:\Ryan\Output_.xls"

    "FullDestinationPath" ( this variable will be in expression)

    Press F4 to get variable expreesion builder and set evaluate as expreesion to TRUE

    then build your expression to get the desired results for file path and name.

    2. get the file syatem task and configured it accordingly.

    Is source a variable to true and then FullSourcePath

    and similar for destination.

    if u do copy file in operation it will have a copy on both location, one with original name and one with the name that is in ur variable expression

    if u do rename file it will actually move file to new location with new name.

    Let me know if u need help with building expression for Destination variable

    it should be simple

    "C:" + "\\" + "NewLocation" + "\\ + "Output_" + "*****put here you code to get date and time portion******" + ".xls"

  • SSIS (7/15/2009)


    OK, so I was able to successfully copy the Excel file from one location to another, but I don't know how to include the date/month in the filename after it's been moved to the new location. Do I need to create variables and if so, how do I do that? I'm just a beginner.

    My package creates a file: C:\Ryan\Output_.xls

    Then that file needs to be moved to a different location, but with the date added to the filename: C:\NewLocation\Output_(PreviousMonth'sName).xls

    Thanks for all the help so far!

    Ryan , little confused here!!!!!. At the very first post u say u want datetime part in filename and now here above you say , you want (PreviousMonth'sName).xls here are the steps that will rename (move and copy file) or copy file to different location using FIle System Task.

    1. Create two variables of type string and assign value accordingly.

    "FullSourcePath" which will have value "C:\Ryan\Output_.xls"

    "FullDestinationPath" ( this variable will be in expression)

    Press F4 to get variable expreesion builder and set evaluate as expreesion to TRUE

    then build your expression to get the desired results for file path and name.

    2. get the file syatem task and configured it accordingly.

    Is source a variable to true and then FullSourcePath

    and similar for destination.

    if u do copy file in operation it will have a copy on both location, one with original name and one with the name that is in ur variable expression

    if u do rename file it will actually move file to new location with new name.

    Let me know if u need help with building expression for Destination variable

    it should be simple

    "C:" + "\\" + "NewLocation" + "\\ + "Output_" + "*****put here you code to get date and time portion******" + ".xls"

    I'm actually following Phil's idea of creating an Output.xls file and then using File System Task to copy/rename it to another location and adding a date to the filename. I was able to copy it to another location, but I'm having trouble adding the date to the filename. Ideally, I'd like to have the name of the previous month included in the filename.

  • Then create one more variable called "Month" as datatype string

    bring Sql Execute task and

    map this variable to this T-SQL

    SELECT DATENAME(mm, GETDATE()) As [Month]

    in the SQL task general page set resultset value as single row and on result set page map this variable as Month to to above variable and sql code to get the month in sqlstatement source is above

    then on destinationpath variable get this expression

    "C:" + "\\" + "NewLocation" + "\\ + "Output_" + @User::Month + ".xls"

  • Here is how I would do it, I think.

    1. Ensure that there is a string variable defined (eg SourceFile) which contains the filepath of the source data (eg C:\Ryan\Output_.xls)

    2. Create a Script Task to do the file copy. Ensure that User:SourceFile is added to the ReadOnlyVariables property of the Script Task.

    3. Replace the entire sample code in your script task with 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

    'Set the file source from the input variable

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

    'Set the file destination as last month + the input variable

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

    '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

    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

  • Phil,

    Do you mean that I don't need any other such as SQL task or data flow task anymore? If so, where should I copy your VB script on script task? I didn't find anywhere that allows me to paste it.

    Thanks a million,

    Jim

  • No problem. Just a script task is needed to take the variable containing the file name and do the file copy for you. This would be on the Control Flow, not the Data Flow.

    Where to paste the code? Double click on the Script Task to open the Script Task editor, select Script and then click on the Design Script button ... You'll find the script in there.

    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

  • 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

Viewing 15 posts - 1 through 15 (of 28 total)

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