CODEWITHSUNDEEP
sql-serversql-server-2008ssisetlssis-2008
Tom
Tom

Reputation: 8681

SSIS Argument SQL for option Copy is not valid

I am trying to do a SSIS package deployment to SQL server 2008. I generated the manifest file but double clicking it didn't open the deployment wizard.

I have tried to execute the dtutil command from the command prompt but getting an error Argument SQL for option Copy is not valid

Following is the command that I am using

enter image description here

I need to deploy to SQL server 2008 R2. I also need to know how and where are the dtsconfig files stored in sql server. Please help

Upvotes: 2

Views: 1748

Answers (3)

Pintu Kawar
Pintu Kawar

Reputation: 2156

To copy a package from a local file system to an instance of SQL Server hosted on another computer, use the following syntax (MSDN):

dtutil /FILE c:\sourcepkg.dtsx /DestServer <servername> /COPY SQL;destpkgname

Graphically you can try by Importing the .dtsx file from local to SQL Server or to the package store. Follow below steps:

  1. Launch SSMS and Connect to Integration Services
  2. Stored Packages>MSDB>Righ Click
  3. Select Import Package and Browse the required file

enter image description here

  1. Select the Protection level as per requirement, Press OK

For Package Configuration, If it is an existing package with dtsConfiguration was in previous SQL Server, Script out the table from previous server and run in new server. You can try new configuraton from below:

enter image description here

Upvotes: 1

Hadi
Hadi

Reputation: 37313

To copy a package from file system to an MSDB database you have to use the following syntax:

dtutil /FILE c:\sourcepkg.dtsx /DestServer <servername> /COPY SQL;destpkgname

So in your case, assuming that the package path is C:\Development\CoreAnalytics\Trunk\src\Willis.CoreReference.ETL\Willis.CoreReference.ETL\bin\Deployment\Core.ReferenceETL.dtsx and that the SQL Server name is MYSERVER\SQL2008R2, so you have to use the following command:

Having a windows Authentication

dtutil /FILE C:\Development\CoreAnalytics\Trunk\src\Willis.CoreReference.ETL\Willis.CoreReference.ETL\bin\Deployment\Core.ReferenceETL.dtsx /DestServer MYSERVER\SQL2008R2 /COPY SQL;CoreReferenceETL

Having a SQL Authentication (user:sa - password:123456)

dtutil /FILE C:\Development\CoreAnalytics\Trunk\src\Willis.CoreReference.ETL\Willis.CoreReference.ETL\bin\Deployment\Core.ReferenceETL.dtsx /DestServer MYSERVER\SQL2008R2  /DestUser sa and /DestPassword 123456 /COPY SQL;CoreReferenceETL

References

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

For me, the most reliable way of deploying a package in SQL Server 2008 has been to import it from the file system using an SSIS connection in SSMS.

  1. Open SSMS and open an Integration Services connection to the server you want to deploy to.
  2. Open Stored Packages and right click the folder where you want to store the package and click "Import Package.."
  3. For Package Location, choose File System.
  4. Click the ellipsis next to Package Path and navigate to your SSIS Solution, go into \bin\release and choose the dtsx file you want to import.
  5. As for the config file, manually place it in the file system. Use the path specified in Package Configurations of the SSIS package.

Upvotes: 0

Related Questions