SQL Server Performance

How do i create a package to export to an excel file and have the file named at runtime

Discussion in 'SQL Server 2005 Integration Services' started by paulze, Mar 18, 2009.

  1. paulze New Member

    I need to run a daily job that will export data from SQL Server to an Excel File. How do i name the destination file dynamically? the file path will always be the same but the file name will be different daily.
    paulze, Mar 18, 2009
    #2

  2. patel_mayur New Member

    Are you following any rule for creating the file name? eg. somefixedcharacter + CurrentDateTime OR it could be anything?
    If you are following some rule then you can create file name run time in SSIS or else you have to take file name as input parameter.
    Regards,
    Mayur.
    patel_mayur, Mar 18, 2009
    #3

  3. paulze New Member

    Would I be able to use the export import wizard to do that ..or do i have to go thru the Business Intelligence Development Studio?
    paulze, Mar 23, 2009
    #4

  4. satya Moderator

    To manipulate the source & destination files with a code or conditions you have to use SSIS whihc can be accomplished by using BIDS, import/export is a simple wizard to manage the ETL activity.
    satya, Mar 24, 2009
    #5

  5. paulze New Member

    thanks satya,
    I will no doubt have questions later on the specifics of how to do it since i am pretty much a novice when it comes to BIDS. But the best way to learn is to dive right in.
    Here is the basic scenario of what i am trying to accomplish. We are a used car warranty business. I'm the database and software developer for the company. Each day a repair center will call in to start a claim against a purchased warranty. And x number of those claims will be approved and authorized each day.
    SalesLogix from Sage Software is the CRM tool we are using to keep tract of those claims. The accounting System is Microsoft Accounting 2009 ( which we just purchased and started using)
    I want to export yesterdays claims to an excel file on the network. The file will be named "Claims[todays date]" I created a view for this purpose and the columns are mapped to the required fields that are in the DP Import tool on the Accounting System. The Account System will then pull the required information from the excel file and import it to the accounting database.
    Right now I run the import export Wizard each day and manually name the excel file with the date extension. I want to set up a daily job to do it.
    Ultimately i want to have a direct mapping from the SalesLogix database to Microsoft Accounting Database without going thru excel but im not yet familar enough with the Account database to do that.
    Any help or suggestions would be greatly appreciated.
    paulze, Mar 24, 2009
    #6

Share This Page