Post reply

Embedded SQL script in deployed package

  • August 27, 2020 at 12:01 pm

    #3784078

    Hi all,

    this may seem to be a silly question. I am full-fledged SQL admin, but SSIS is a topic I maybe touch in regards to setting permissions or general execution issues.

    I am now faced with a customer (and due to some circumstances in my current work situation, I have to deal with him) who has a project with a package inside where a SQL script is linked as file in the connection manager. When he uploads it to the server, the step that refers to the script works fine although the path is not valid on target server (I assume the file is somehow embedded into the package). Another colleague is now complaining because, according to him, he is doing exactly the same - but his execution gets an error that the SQL script can not be found (locally C:\Temp\XYZ which is not existing on target server). For now I copied his file manually to the respective path (so that it matches the path that is initially in the connection), but in the end he wants to know why his execution fails while the one from his colleague does not.

    I tried for two hours getting information via Google, but am still lost. Anyone able to help me here? I hope this is just a general thing, everything else is probably way too much to ask here.

    Thanks in advance and greetings from Germany

    Stefan

  • August 27, 2020 at 2:11 pm

    #3784136

    I suspect that there is some configuration on the server where the script is working which overrides the default (and invalid) path. It is unlikely that the SQL script is embedded in the package, in my opinion.

    There are several ways to do this. To determine which is being used here, I need to ask a few questions:

    1. Has the working package been deployed to SSISDB? If not, is it deployed to MSDB or just the file system?
    2. Is the package being executed by a SQL Agent job? If not, how is it executed?

    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

  • August 31, 2020 at 7:55 am

    #3784823

    Hi Phil,

    thank you for your response.

     

    1. Package is deployed to SSISDB (via Visual Studio by customer), not MSDB.
    2. Directly executed via SSISDB context and via Agent Job (in context of a proxy due to lack of sysadmin).

    Kind regards

    Stefan

    P.S.: I found one thing, the packages from colleague A (the ones working) have the scripts as "directinput" whereas the packages from colleague B (the ones throwing error path not found) have it as "fileinput". He swears both are doing the same steps and colleague A did not adjust anything. Both are starting from the same Visual Studio project. Is there a possibility that Visual Studio is somehow automatically adjusting this so that the linked .sql file is inserted as directinput?

    • This reply was modified 4 years, 2 months ago by  shp.shftr.
    • This reply was modified 4 years, 2 months ago by  shp.shftr.
    • This reply was modified 4 years, 2 months ago by  shp.shftr.

  • August 31, 2020 at 5:47 pm

    #3784979

    I found one thing, the packages from colleague A (the ones working) have the scripts as "directinput" whereas the packages from colleague B (the ones throwing error path not found) have it as "fileinput". He swears both are doing the same steps and colleague A did not adjust anything. Both are starting from the same Visual Studio project. Is there a possibility that Visual Studio is somehow automatically adjusting this so that the linked .sql file is inserted as directinput?

    This is a critical point. VS does not adjust this property unless you tell it to do so.

    I suggest that you check a couple of things, just to collect some useful additional info:

    1. In SSMS, right click on the project containing the deployed package (under the 'Integration Services Catalogs' node) and select 'Configure'. In the dialog that appears, choose 'References' under ''Select a page'. Do you see anything there? If yes, there may be parameter and connection overrides here which need to be looked at.
    2. Also in SSMS, find the job which executes the SSIS package and get the properties for the job step which executes the package. Select the Configuration tab and look for any parameters or connections which are being overridden 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

Viewing 4 posts - 1 through 3 (of 3 total)

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