none
Deployed Project not importing CSV file Data

    Dotaz

  • I have a project with an Execute SQL Task (Which truncates the temp table when the project is run), a Foreach Loop Container which contains a Data Flow task to import the results of CSV files into the temp table. I then have outside of the Foreach Loop Container, a Script Task that deletes the CSV files from a specified folder directory. When I run the project in debug mode, it works perfectly. The temp table is truncated, the results of multiple CSV files is imported into the Temp table, and the CSV files are deleted. However, when I deploy the project and create a SQL Agent Job, the truncate table task runs as well as the Script task that deletes the files. But there is no data that is imported. It's as if the Data Flow Task inside the Foreach Loop Container does nothing. Any ideas? I'm using 2012.
    pátek 6. července 2018 20:21

Všechny reakce


  • Right click sql agent job and go to view history

    Go to the step which has the issue and check for "Executed as user: " this login need to be added to the folder where you are trying to access the files in the ForEach loop container.

    And Try to run the sql agent job again.


    mohammad waheed

    pátek 6. července 2018 20:44
  • The executed as user is a Domain user and all domain users have full control of that folder.
    pátek 6. července 2018 20:56
  • The executed as user is a Domain user and all domain users have full control of that folder.

    Set parameters for passing the values. Set values for them through the job as explained in the earlier thread

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55670d30-f60b-4991-99b9-6c4fc40c17fc/bulk-insert-from-multiple-csv-files?forum=transactsql#0c2f909a-7408-443e-a6bf-073a3041d131


    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

    pátek 6. července 2018 21:15
  • The package works when I right click on it in SSMS and click execute.

    However, when I create the Job in SQL Server Agent, it still does not load the data.

    pondělí 9. července 2018 18:33
  • Hi BassMan75,

    Have you tried to switch runtime mode (32/64)?

    You can Script your job as to new.. to find the underlying Dtexec command and compare with the command which generated by Execute Button from Catalog.

    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.

    úterý 10. července 2018 7:11
  • I see where to script the job as new and see the underlying code behind it, but how do I go about seeing the code for the Execute option from the catalog in SSMS?
    úterý 10. července 2018 15:59
  • I see where to script the job as new and see the underlying code behind it, but how do I go about seeing the code for the Execute option from the catalog in SSMS?

    I dont think that will solve the issue you're facing

    Because even when you right click the package and execute it from SSMS, its getting executed under the context of the account you used to login in SSMS. Your real issue relates to the inability of SQL Server Agent service account in performing some actions which are performed by the same package under your accounts context. It can be permission issues in accessing a folder, executing an application, etc so needs to resolved on comparing differences in the two execution contexts. Executing under your account ia always going to work fine based on the explanation

    That being said, to script out execute option, choose the execute option and in the next screen use the script option on top menu as shown


    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ý 10. července 2018 18:41
  • Thanks, nothing is really making sense at this point. The package was created using the same domain account that is logged into SSMS, which has access to all directories. I even scripted the execute out and copied that command into a Job, ran the job and still doesn't work. The SQL Server Agent is logged in as the same domain account as above. I can save the script as a Sproc and execute the Sproc and everything works great. As soon as I create a Job, nothing works when referencing the project. This is crazy.
    úterý 10. července 2018 19:37
  • Hi BassMan75,

    Yes, it is odd. 

    Try to use proxy account which has sufficient permission to access both source and destination and execute permission.

    Or export the ssis package in file system and directly call the package with command via job.

    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 11. července 2018 7:44
  • Thanks, nothing is really making sense at this point. The package was created using the same domain account that is logged into SSMS, which has access to all directories. I even scripted the execute out and copied that command into a Job, ran the job and still doesn't work. The SQL Server Agent is logged in as the same domain account as above. I can save the script as a Sproc and execute the Sproc and everything works great. As soon as I create a Job, nothing works when referencing the project. This is crazy.

    Still the job will not use the same domain account you used to create/execute package from SSMS. Job uses its default service account for executing the steps (which is what I've been telling since the start of this thread :) )

    Now for an easy resolution, what you can do is to add a proxy account based on your domain account and configure your job step to use it instead.

    To understand how you can do it refer

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

    replace ARSHADALI-LAP\SSISProxyDemoUser  account with your domain account in the example which link specified


    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 11. července 2018 7:59
  • Still not working, even with a Proxy account. The steps of truncating the temp table and deleting the .csv files from the folder are working, but the contents of the .csv files are not being imported via the Job. In running the scripts in the example you provided, I discovered that the domain account I have been using is a member of the sysadmin role. This is when I have the Package Source set as SSIS Catalog. On the other hand, while following along with your example provided, I changed the job to Package Source of File System

    the SQL Agent Job ran as Package Source File System successfully, however, the csv files were not loaded into the dbase table. Same results as previous.


    • Upravený BassMan75 středa 11. července 2018 18:57
    středa 11. července 2018 13:02
  • Hi BassMan75,

    I suspect the issue may be caused by the flat file component that reads data from .csv file. 

    Try to use other component e.g. Jet OLE DB or replace .csv file with .txt file to have a check.

    SSIS FlatFile Access via Jet

    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.

    čtvrtek 12. července 2018 8:05
  • I'll give that a try, but doesn't explain why the package works perfect when you right click and execute from SSMS. But as soon as I create a SQL Server Agent Job, the job is successful, but it's like the Job skips over the importing of the csv files. Like I said above, I tried a proxy account, the login I was using was already a member of sysadmin, has full control of the target folder where the csv files are stored. Very weird. I'm wondering if it's a shortcoming of SQL 2012. At any rate, I will try your suggestions and see if I can get anywhere. In the meantime, any other thoughts are welcome.
    čtvrtek 12. července 2018 12:42
  • I'll give that a try, but doesn't explain why the package works perfect when you right click and execute from SSMS. But as soon as I create a SQL Server Agent Job, the job is successful, but it's like the Job skips over the importing of the csv files. Like I said above, I tried a proxy account, the login I was using was already a member of sysadmin, has full control of the target folder where the csv files are stored. Very weird. I'm wondering if it's a shortcoming of SQL 2012. At any rate, I will try your suggestions and see if I can get anywhere. In the meantime, any other thoughts are welcome.

    Ok 

    One more thing

    You told you're using 2012 which I believe is the version of SSIS installed on the server

    Which version of SSDT you used to create the package? 


    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

    čtvrtek 12. července 2018 13:00
  • čtvrtek 12. července 2018 13:55