none
individual ssis package runs and loads data, but fails to load when deployed

    Question

  • I have  an SSIS package that reads a series of .csv files and loads into a sql server table...

    it works fine individually, but when i deploy it and when i try to run it from ssisdb\ path,  i get a warning that files cannot be found and job completes fine but nothing is loaded.

    Now in my individual ssis package, I specify what files to pick up in the parameter, and then the "Files" section of For each loop container gets automatically populated with whatever parameter I enter .csv...why is it not working when I deploy it 

    Monday, April 15, 2019 8:16 PM

All replies

  • Basically, the Filespec expression in the collection of ForEachLoop has the value that the ssis package works with to read the files

    now when I deploy it in ssisdb, should i define the same there ?

    Monday, April 15, 2019 8:26 PM
  • so I deployed the pacakge in ssisdb path

    then i created a sql server job agent which refers to the above package from the path

    now, how can I configure the filespec in the job agent so the foreachloop will look for the files in the folder ?

    Monday, April 15, 2019 8:29 PM
  • so I deployed the pacakge in ssisdb path

    then i created a sql server job agent which refers to the above package from the path

    now, how can I configure the filespec in the job agent so the foreachloop will look for the files in the folder ?

    Yes

    You can create a parameter inside package and map it to an environment variable. Then through the variable you pass value for filespec property of the for each loop

    see

    https://www.sqlchick.com/entries/2015/1/4/parameterizing-connections-and-values-at-runtime-using-ssis-environment-variables


    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

    Monday, April 15, 2019 8:41 PM
  • Strangely, I don't see any Variables when I click on 'Environment" 
    Monday, April 15, 2019 8:56 PM
  • OK, I created environment and created variables based on the parameters from the package
    Monday, April 15, 2019 9:02 PM
  • 1. Package has 3 parameters

    1. ReportFilePath : value is network path where i can find my files

    2. ReportMonth:  201902

    Now package works fine, after reading your reply, I added a new parameter as below

    In the package I created a new parameter p_Filespec where I wrote an expression to get  to the path of the file

    Note: Expression because to give the month and year dynamically, file names are like abc201902.CSV 

    "*abc" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"

    the above is taken from Filespec --> using the same value for the new parameter

    ReportMonth is another parameter where user has to enter what is the year and month for which the files needs to be picked,

    2. Now I deploy this in ssisdb, and as you suggested, created an environment variable , in total 3 parameters, so 3 variables

    1. EV_FILESPEC - "*abc" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"

    2. EV_MONTH - 201902

    3. EV_PATH - my network folder path where the above file (number of files for the given month) are there

    when I configure the projects, use Environment variable is disabled, I don't understand the flow of this and how it will help me pick up the files from sql server management studio

    Also, do you think it has to do with the permissions ? the file is in network drive and dba gave me access to the network drive, only after that my individual package ran fine.

    should permissions be checked if this dev server has access to the network drive ? maybe this is the issue ?


    • Edited by MSBI1980 Monday, April 15, 2019 9:29 PM
    Monday, April 15, 2019 9:24 PM
  • Hi MSBI1980,

    when I configure the projects, use Environment variable is disabled,

    May I know if it is like the following picture shown?

    May I know if you try to add the Environment when you configure the projects?

    After adding the environment, we could use environment variable.

    Best Regards,

    Mona Lv


    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

    Tuesday, April 16, 2019 9:03 AM
  • Thanks,

    So I added the environment and as seen in the screenshots, i used environment variable to configure at 2 levels, project level and package level

    even then, when I try to execute the package from ssisdb, i get error saying that parameter is not set 

    package level

    project level

    Tuesday, April 16, 2019 1:55 PM
  • OH, I have to check the environment checkbox, just did that, let me check if its working now
    Tuesday, April 16, 2019 1:57 PM
  • ok, do I have to do another step to make the checking of Environment checkbox permanent ? Everytime I check the below checkbox it still appears unchecked and I keep getting above error

    Tuesday, April 16, 2019 2:02 PM
  • ok, how can I reference one environment variable in another

    problem is, when i check the environment checkbox above, the package is getting executed but still not able to find the files...

    is it because in env variable ev_filespec i am using value from package

    "*abc-" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"

    i think this is why its not picking up files, should I use the environment variable ev_month in above, if so how ?

    Tuesday, April 16, 2019 2:09 PM
  • Can someone pls help ? why is that Environment checkbox is not being checked by itself ? 

    and how can I specity dynamically the files ? , files will be in the format of

    abc-YYYYMM.CSV

    Tuesday, April 16, 2019 7:41 PM
  • Hi MSBI1980,

    According to your issue, we could use variable FileSPEC.

    The value of the variable FileSPEC will be changed when you change the value of parameter ReportMonth.

    why is that Environment checkbox is not being checked by itself ? 

    Please give suggestion to improve SQL Server here: https://feedback.azure.com/forums/908035-sql-server

    Best Regards,

    Mona Lv


    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

    Thursday, April 18, 2019 10:21 AM
  • Mona, my ssis package works perfectly fine when it runs individually before deployment into ssis catalog in the development server...

    the problem occurs when I deploy the ssis package in the server, i then tried the environment variable approach but that did not help, as highlighted in my earlier messages...

    do you think the account used to login to the dev server does not have access to the files in the network drive ? mind you, the drive where files are present is a secured network drive ..my dba gave access to my windows login so it worked in the ssis package..problem is the package doesn't work when deployed in dev server..

    this is frustating

    Thursday, April 18, 2019 4:43 PM
  • Hi MSBI1980,

    1.Please check if the account executing the package has access to the files in the network drive .

    We could see the account executing the package in reports for execution in Catalog or view history in Job.


    2.

    1. EV_FILESPEC - "*abc" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"

    In the package I created a new parameter p_Filespec where I wrote an expression to get  to the path of the file

    Note: Expression because to give the month and year dynamically, file names are like abc201902.CSV 

    "*abc" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"

    It seemed that the parameter p_Filespec  is not useful here.

    The value of Files in foreach loop will be "*abc" + (DT_STR, 6 , 1252)@[$Package::ReportMonth] + "*.CSV"

    We could put "*abc" + (DT_STR, 6 , 1252) @[$Package::ReportMonth]  + "*.CSV"  in the FileSpec expression.

    The value of Files in foreach loop will be *abc201903*.CSV


    Best Regards,

    Mona Lv


    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


    Friday, April 19, 2019 11:48 AM
  • for point 2, I am following the second approach and its working for me in the package level, so should I stop trying creating environment variables ? 

    Friday, April 19, 2019 3:38 PM
  • for point 2, I am following the second approach and its working for me in the package level, so should I stop trying creating environment variables ? 

    Hi MSBI1980,

    Yes. It's better for you to stop creating parameter p_Filespec and environment variable EV_FILESPEC .

    May I know if your package can run successfully in SSISDB catalog and if the foreach loop container can find files?


    Best Regards,

    Mona Lv


    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

    Monday, April 22, 2019 6:13 AM
  • Hi Mona, Thank you for your responses, No at ssisdb catalog the for each loop is not picking up files, I created a ticket for the sql server dbas or someone who has production access to check the permissions.
    Monday, April 22, 2019 1:41 PM
  • I am working with dba and this will be resolved, thanks everyone for your help
    Monday, April 22, 2019 8:44 PM
  • Hi MSBI1980,

    Please remember to click "Mark as Answer" the responses that resolved your issue. This can be beneficial to other community members reading this thread. 

    Best Regards,

    Mona Lv


    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

    Wednesday, April 24, 2019 1:30 AM