none
SSIS Package: How to export data to CSV file

    Dotaz

  • Hi All,

    I am trying to export Data from SQL to CSV file.

    I went trough the below link and are able to create Successfully create the package. Tested it directly from the Visual studio and it works fine.

    http://bpmdeveloper.com/ssis-package-export-data-to-csv-file/

    The part i am struggling with is to create a batch file. 

    http://bpmdeveloper.com/dtexec-execute-ssis-package/

    úterý 15. května 2018 13:02

Všechny reakce

  • Sorry why do you need batch file?

    You can directly schedule and execute the package from SQL Server Agent job.

    If SSIS 2012 or above you can also call and execute SSIS package using catalog system procedures available in SSIS Catalog.

    If you really want to go with bat file, then can you post what was the error message when you tried it?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    úterý 15. května 2018 13:06
  • Hi roma_victa,

    What's the error message you got when using a batch file to run SSIS package?

    One possible cause is you used 32/64 bit DTexec to execute 64/32 bit package, please double check and ensure the version of tool is proper.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 16. května 2018 2:41
  • i am going to have to give the package to my colleague who will be running to get the CSV on a regular basis.

    I thought by creating a batch file things will be easy for them.

    Regards

    Roma

    středa 16. května 2018 5:27
  • i am going to have to give the package to my colleague who will be running to get the CSV on a regular basis.

    I thought by creating a batch file things will be easy for them.

    Regards

    Roma

    Nope

    Even by creating a procedure to call and execute the package using SSISDB catalog procedures like create_execution, your colleague would be able to execute the package by executing the procedure. The only thing is to have his credentials mapped to corresponding roles in SSISDB if he's not a dbo user already


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 16. května 2018 5:33
  • I am not getting any error. I can run directly from visual studio and the CSV gets updated. I have only edited the code replaced the path with mine. 

    I am using 64 bit windows 7 how can i check what is advised and what is the correct settings for me?

    středa 16. května 2018 5:44
  • I am not getting any error. I can run directly from visual studio and the CSV gets updated. I have only edited the code replaced the path with mine. 

    I am using 64 bit windows 7 how can i check what is advised and what is the correct settings for me?

    you still didnt tell us the details on the error you got while running the batch file

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 16. května 2018 5:55
  • Hi please see the code below which i copied and pased from the second link

    when it runs i get a prompt to provide Y/N. That is about it.

    Can i simply create an exe or a setup file so that i can install it to my colleagues ? as and when they want the csv file they can simply run the application to extract the details from the database to a CSV file

    @echo off
    REM Check if there is an already temporary file, if YES delete it
    if EXIST C:\ExportedDocuments\Temp\exported.csv goto STEP1
    :STEP1
    ECHO Temporary file exists, delete file
    DEL C:\ExportedDocuments\Temp\Project.CSV
    REM Execute the SSIS Package with Configuration file
    dtexec /f"C:\Users\ITsqlsyncservice\Documents\Visual Studio 2010\Projects\VisionDBToCSV\VisionDBToCSV\Package.dtsx" /conf"C:\Users\ITsqlsyncservice\Desktop\Config.dtsConfig"
    REM Get current date: DDMMYYYY format
    SET dt = %date:~-10,2%%date:~-7,2%%date:~-4,4%
    SET TEMP = C:\ExportedDocuments\Project.CSV
    SET PATH = C:\ExportedDocuments\Exported_%dt%.csv
    @ECHO ON
    REM Copy the temporary CSV file to a new location with a different name
    COPY %TEMP% %PATH%
    REM Delete the temporary file
    DEL %TEMP%
    

    for some reason the link i provided in my Question is not working now. Please see below for what i have done in Visual studio so far. if i can create a setup or exe file which runs on the users pc which converts the sql database to CSV file. That is all i need.

    https://www.youtube.com/watch?v=n94-pFXP8eA

    středa 16. května 2018 6:33
  • Hi roma-victa,

    Do other machines install the SSIS?

    Running with SSIS packages with full supported feature, you need to install SSIS on machines. 

    Otherwise, currently, you can only run the packages remotely using SQL Agent or Web Service.

    Assume you have installed the SSIS on all of machines, you need to use the UNC path to specify the location of package. Also, you need sufficient permissions to call DTEXEC to execute the package. 

    In short, create a setup or exe file is possible to run the package on other PC, but you must meet the requirements as mentioned before.

    Loading and Running a Remote Package Programmatically

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 16. května 2018 7:44
  • i see, Thanks Pirlo,

    No one has SSIS

    I thought if i supply the batch file with the above code and provide Package.dtsx and the CSV file the users will be able to do it by themselves? . Please excuse my ignorance. 

    Is there any way that we can set this up automatically so that every morning the server creates and save the .CSV file in a public location ?

    středa 16. května 2018 7:52
  • i see, Thanks Pirlo,

    No one has SSIS

    I thought if i supply the batch file with the above code and provide Package.dtsx and the CSV file the users will be able to do it by themselves? . Please excuse my ignorance. 

    Is there any way that we can set this up automatically so that every morning the server creates and save the .CSV file in a public location ?

    For executing SSIS package in their machines, they need to have SSIS runtime installed


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 16. května 2018 7:56
  • Hi roma_victa,

    You can use SQL Agent job/Windows task Scheduler to run the batch file with schedule.

    Or call the SSIS package directly via SQL Agent job using SSIS type in job step properties.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 16. května 2018 7:58
  • 1)You can use SQL Agent job/Windows task Scheduler to run the batch file with schedule. The problem is that i am unable to get the batch file to work as mentioned above. 2)Call the SSIS package directly via SQL Agent job using SSIS type in job step properties. can you provide any examples or reference to this?
    pondělí 21. května 2018 7:39
  • 1)You can use SQL Agent job/Windows task Scheduler to run the batch file with schedule. The problem is that i am unable to get the batch file to work as mentioned above. 2)Call the SSIS package directly via SQL Agent job using SSIS type in job step properties. can you provide any examples or reference to this?

    If you're storing SSIS in SQLServer (MSDB)

    see

    https://www.mssqltips.com/sqlservertutorial/220/scheduling-ssis-packages-with-sql-server-agent/

    If you're storing the packages in SSISDB (Integration Services Catalog)

    see

    https://www.mssqltips.com/sqlservertutorial/9069/deploy-and-schedule-an-sql-server-integration-services-ssis-package/

    Both the cases you would require the proxy account created to have required permissions to access and execute the batch file

    https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    pondělí 21. května 2018 8:24