SSIS Package created in SQL 2008

  • Hello Everyone

    I have some SSIS packages that were created in SQL 2008, I am moving all the packages to SQL 2012. What should I look for, or be aware of when I create the SQL jobs in SQL 2012 for the 2008 packages? I have already found the Provider=SQLNCLI10.1 to Provider=SQLNCLI11.1

    Does anyone have a sample of the command line? The one that SQL Job creates is giving me a warning.

    Here is what SQL generated:

    /DTS "\"\MSDB\TestAndDev\Andrew\"" /SERVER Mistress /CONFIGFILE "\"D:\TestAndDev\SSIS Packages\Andrew\XMLConfig\TestAndDev_XMLConfig.dtsConfig\"" /WARNASERROR /VALIDATE /REPORTING E

    But the Pre Execute validation is giving an error in the command line. When I execute the package, it indicates a Success, but does not get past the second step, which is a ScriptTask

    Thanks in advance

    Andrew SQLDBA

  • If I understand what you are asking what you are looking for is DTEXEC.exec at the beginning of the command.

    I did a blog on creating SSIS commands here[/url] that may help you out.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • I think that what you have on your website is not really what I need. I am looking for a command line for pulling the package from a SQL store, or SQL Server.

    /DTS "\"\MSDB\TestAndDev\Andrew\"" /SERVER Mistress /CONFIGFILE "\"D:\TestAndDev\SSIS Packages\Andrew\XMLConfig\TestAndDev_XMLConfig.dtsConfig\"" /WARNASERROR /VALIDATE /REPORTING E

    The package is not executing the ScriptTask that is the second step in the package. Is there anything funky with SSIS 2012 executing 2008 C# ? Or anything else that you can think of that would cause the issue of not executing that step?

    Thanks

    Andrew SQLDBA

  • Not that I know of. Do you have the specific error?

    Just a thought. If you haven't already done this you might try making a backup copy and opening the package up in the 2012 tools. It will want to upgrade it. Upgrade it and see if that fixes your problem. I know that the packages are not completely compatible between versions of Visual Studio so it isn't a big leap to think that you have to have an upgraded version to run correctly from a 2012 job.

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • AndrewSQLDBA (9/9/2013)


    I think that what you have on your website is not really what I need. I am looking for a command line for pulling the package from a SQL store, or SQL Server.

    /DTS "\"\MSDB\TestAndDev\Andrew\"" /SERVER Mistress /CONFIGFILE "\"D:\TestAndDev\SSIS Packages\Andrew\XMLConfig\TestAndDev_XMLConfig.dtsConfig\"" /WARNASERROR /VALIDATE /REPORTING E

    The package is not executing the ScriptTask that is the second step in the package. Is there anything funky with SSIS 2012 executing 2008 C# ? Or anything else that you can think of that would cause the issue of not executing that step?

    Thanks

    Andrew SQLDBA

    You might want to open the script task in the SSIS 2012 editor and close it again, so it gets recompiled.

    Which deployment model are you using in SSIS 2012?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hello Everyone

    I found out that one Must use BIDS 2012, or what ever name it has now, to convert the 2008 package(s) before they can be used in SSIS 2012. Not a big thing, but it does change some things.

    I am not certain about the deployment methods. I have always copied the package manually and imported it into the MSDB database. What are the two new methods, and are they required? I do not like using anything "automated" to do too much on a production server.

    Thanks

    Andrew SQLDBA

  • The deployment models are they way SSIS packages are developed in SSIS 2012.

    You have two models:

    * the package deployment model: the unit of deployment is a single package. This is how it has always been since SSIS 2005.

    * the project deployment model: packages now reside in a project. You can only deploy projects, not individual packages. You can use the new parameters and project connection managers.

    More info: http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012-part-1-of-2/

    ps: what's wrong with doing automated stuff on a production server? I don't like anything "manual" on a production server.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 7 posts - 1 through 6 (of 6 total)

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