SSIS - script component as source error

  • Hi, I am at a bit of a loss here and really hoping someone can help.

    I have an SSIS package that has a script component as source, it calls an API to pull data across the web and into SQL Server.

    When I add or remove a column from the script when on my laptop and then try and run the package on the server, I get the following error message: 'The component metedata is out of sync with the compiled script. Recompile the script using the Script Component Editor'... I then have to open the script while on the server, recompile the script and deploy again.

    The funny thing is that when I add or remove a column while on the server and then open the same package from my laptop, it runs fine without any issues. It only appears to be when going from my laptop to the server.

    I am using Visual Studio 2015 on both my laptop and server and SQL Server 2014 (SP2) on both my laptop and server.

  • I'm a little confused about your statement of adding/removing a column on your laptop/server. How are you removing a column on the package on the server if it's deployed (are you not using the SSIS catalog)?

    It is expected that if you remove a column of a Script Task that the metadata will be out of sync. The task will still be expecting it, and if you don't update the metadata 9which can be done by simply opening, saving and closing the script), then the script will fail (as effectively an expected object doesn't exist).

    The main thing is, however, that you should expect the metadata to be out of sync when doing such tasks. Much like if you remove (or add) a column in an Excel document and then get SSIS to run. Even if all the other columns still exist, the component will still fall over, due to metadata issues.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A (12/22/2016)


    I'm a little confused about your statement of adding/removing a column on your laptop/server. How are you removing a column on the package on the server if it's deployed (are you not using the SSIS catalog)?

    I am logging onto the server with a remote desktop connection and editing the package directly in visual studio (I have to do this, if I deploy directly from my laptop to the server I get the error message as mentioned previously)

    Thom A (12/22/2016)


    It is expected that if you remove a column of a Script Task that the metadata will be out of sync. The task will still be expecting it, and if you don't update the metadata which can be done by simply opening, saving and closing the script), then the script will fail (as effectively an expected object doesn't exist).

    I agree, but when I make the changes on my laptop I am recompiling the script and it runs fine on my laptop, the problem is that when I deploy the package to the server, I need to open up the script task again while on the server and recompile a 2nd time otherwise I get the error message about the metadata being out of sync.

    *edit* Sorry should have mentioned, we aren't deploying to the catalog, we are still using the package deployment model at the moment but i'm not sure this will have anything to do with the issue.

    Thanks

  • Jim-S (12/22/2016)


    *edit* Sorry should have mentioned, we aren't deploying to the catalog, we are still using the package deployment model at the moment but i'm not sure this will have anything to do with the issue.

    Thanks

    That was due to my confusion of you "editting the package on the server". I had an image of you "diving" into the catalog and editting it there. Something I'm pretty sure you can't do 😎

    As for why it happens in one place and not the other, I don't know. As I said, you should expect it to, as it stops anything unexpected happening (as diverse as SSIS is, it's very rigid so any deviation from the defined path can cause all sorts of unexpected events), so the case is more why it's not happening. I assume it's more of a small hindrance anyway, than anything, and isn't causing any real problems?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Unfortunately it is causing us some issues, we are using Microsofts ALM (kanban model) to deploy our stuff to test and live servers. We attach our dtsx files/ code to a kanban card and some powershell scripts come along and deploy it automatically. As this issue is preventing us from deploying directly from our local machine to server without recompiling on the server first, then this is going to prevent us from deploying these particular packages via our current process (unless of course we do the development on the server itself and use the dtsx file from the server).

    I will keep looking but unfortunately there isn't a great deal of information out there. Thanks for your help.

  • Jim-S (12/22/2016)


    Unfortunately it is causing us some issues, we are using Microsofts ALM (kanban model) to deploy our stuff to test and live servers. We attach our dtsx files/ code to a kanban card and some powershell scripts come along and deploy it automatically. As this issue is preventing us from deploying directly from our local machine to server without recompiling on the server first, then this is going to prevent us from deploying these particular packages via our current process (unless of course we do the development on the server itself and use the dtsx file from the server).

    I will keep looking but unfortunately there isn't a great deal of information out there. Thanks for your help.

    Maybe it's time to consider the project deployment model. Get your ALM process working so that stuff compiles on your CI server and deploys to SSISDB - I've been working like this for years and don't think I've ever had the problem you are having.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I'm wondering if perhaps you have a SQL Server version difference between your laptop and the server itself. I recently ran into some SQL 2012 SSIS packages that were deployed as files that started failing on a SQL 2012 instance that had a version of SQL 2012 that was beyond the RTM version, and had been created using the RTM version. Knowing that such took place, I have to wonder if something of that nature could be affecting you. All I needed to do was to edit those packages on the server, without making any changes, and then just re-save them, and they ran fine. Let us know if you find a version difference, and what the two different versions are.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hi sgmunson

    I should probably have mentioned this before, but I don't have a local instance installed on my laptop, I always connect to the server.

    I'm guessing we can rule out an issue with SQL server versions as the ssis package connects to the same instance from both my laptop and server.

  • Phil Parkin (12/22/2016)


    Jim-S (12/22/2016)


    Unfortunately it is causing us some issues, we are using Microsofts ALM (kanban model) to deploy our stuff to test and live servers. We attach our dtsx files/ code to a kanban card and some powershell scripts come along and deploy it automatically. As this issue is preventing us from deploying directly from our local machine to server without recompiling on the server first, then this is going to prevent us from deploying these particular packages via our current process (unless of course we do the development on the server itself and use the dtsx file from the server).

    I will keep looking but unfortunately there isn't a great deal of information out there. Thanks for your help.

    Maybe it's time to consider the project deployment model. Get your ALM process working so that stuff compiles on your CI server and deploys to SSISDB - I've been working like this for years and don't think I've ever had the problem you are having.

    The ALM stuff is a bit over my head but I will have a chat with the guy who deals with this. We have only just upgraded from 2008 and I believe we will be looking at the project deployment model soon.

  • Jim-S (1/3/2017)


    Phil Parkin (12/22/2016)


    Jim-S (12/22/2016)


    Unfortunately it is causing us some issues, we are using Microsofts ALM (kanban model) to deploy our stuff to test and live servers. We attach our dtsx files/ code to a kanban card and some powershell scripts come along and deploy it automatically. As this issue is preventing us from deploying directly from our local machine to server without recompiling on the server first, then this is going to prevent us from deploying these particular packages via our current process (unless of course we do the development on the server itself and use the dtsx file from the server).

    I will keep looking but unfortunately there isn't a great deal of information out there. Thanks for your help.

    Maybe it's time to consider the project deployment model. Get your ALM process working so that stuff compiles on your CI server and deploys to SSISDB - I've been working like this for years and don't think I've ever had the problem you are having.

    The ALM stuff is a bit over my head but I will have a chat with the guy who deals with this. We have only just upgraded from 2008 and I believe we will be looking at the project deployment model soon.

    Any chance no one installed the upgraded version of SQL Server Data Tools ? The SSIS editor that comes with SQL 2008 versions is called BIDS, and back then, it wasn't referred to as SSDT (SQL Server Data Tools). It is still a Visual Studio tool, but the version of Visual Studio that SQL 2014 uses is the 2013 version as opposed to whatever really old version SQL 2008 used for BIDS. In any case, if that has NOT occurred, or it occurred, but the old program is still being used, that might account for the problem.

    EDIT: Just noticed you're previous post about using Visual Studio 2015. That might be a version difference, as I believe SSDT for SQL 2014 uses 2013. It MIGHT account for the problem. If you're not using SSDT, that could be it as well.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (1/3/2017)


    Jim-S (1/3/2017)


    Phil Parkin (12/22/2016)


    Jim-S (12/22/2016)


    Unfortunately it is causing us some issues, we are using Microsofts ALM (kanban model) to deploy our stuff to test and live servers. We attach our dtsx files/ code to a kanban card and some powershell scripts come along and deploy it automatically. As this issue is preventing us from deploying directly from our local machine to server without recompiling on the server first, then this is going to prevent us from deploying these particular packages via our current process (unless of course we do the development on the server itself and use the dtsx file from the server).

    I will keep looking but unfortunately there isn't a great deal of information out there. Thanks for your help.

    Maybe it's time to consider the project deployment model. Get your ALM process working so that stuff compiles on your CI server and deploys to SSISDB - I've been working like this for years and don't think I've ever had the problem you are having.

    The ALM stuff is a bit over my head but I will have a chat with the guy who deals with this. We have only just upgraded from 2008 and I believe we will be looking at the project deployment model soon.

    Any chance no one installed the upgraded version of SQL Server Data Tools ? The SSIS editor that comes with SQL 2008 versions is called BIDS, and back then, it wasn't referred to as SSDT (SQL Server Data Tools). It is still a Visual Studio tool, but the version of Visual Studio that SQL 2014 uses is the 2013 version as opposed to whatever really old version SQL 2008 used for BIDS. In any case, if that has NOT occurred, or it occurred, but the old program is still being used, that might account for the problem.

    EDIT: Just noticed you're previous post about using Visual Studio 2015. That might be a version difference, as I believe SSDT for SQL 2014 uses 2013. It MIGHT account for the problem. If you're not using SSDT, that could be it as well.

    Ok so I am using SSDT (BI) 2015 with the TargetServerVersion property set to SQL Server 2014

Viewing 11 posts - 1 through 10 (of 10 total)

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