Link to home
Create AccountLog in
Avatar of hvapro
hvapro

asked on

Deploy SSIS/DTS package

Hi,

We are using SQL Server 2005, and I like to know how can I deploy a SSIS.dtsx package on the server and schedule the job to run the package?

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of hvapro
hvapro

ASKER

Thanks for your prompt rely, can you tell me what is the steps to deploy the package?

Below is the steps that I have done:
1, built the package and save it on my local machine
2, deploy the package (SSISDeploymentManifest) that just buit to the server and the selected installer folder is c:\Program Files\Microsoft SQL Server\90\DTS\Packages\FTPIS
3, here I got the problem,  after successfully deployed the package there is nothing under the selected installer directory which is "c:\Program Files\Microsoft SQL Server\90\DTS\Packages\FTPIS"

Do you know what is causing the problem?

Thanks
You're better off saving the .DTSX file (where you created the project, after you have built it), and using that in the job rather than finding it on the sql machine.  


http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx
Dear Friend,
You can go to SSIS server and import directly the package.
1. Right click in your SSIS Project and go to properties.
2. IN the tab Configuration Propertires > Deployment Utility, change the createdeploymentutility to TRUE.
3. Inside your folder  app.Path & \Bin\Deployment you have the package deployed.
4. Right-Click and follow the instructions to deploy it.
Cheers!
Avatar of hvapro

ASKER

Hi,

I followed your steps above and deploy my package to the server and it gave me the following error:

TITLE: Package Installation Wizard
------------------------------
Could not save the package "C:\Documents and Settings\Desktop\Integration Services Project1\bin\Deployment\Package.dtsx" to SQL Server "myServerName".
------------------------------
ADDITIONAL INFORMATION:
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.).  The SQL statement that was issued has failed.
------------------------------
The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.).  The SQL statement that was issued has failed.
------------------------------
BUTTONS:
OK
------------------------------


Thanks,



Do you tried open your SSIS instance and import directly from there, using the right click of mouse?
I have some post ion my blog explain a similar case...
www.pedrocgd.blogspot.com
I hope it helped!
Regards!
Any improvements?!
Improvements?
If not, tell me!!
Cheers
I found you! :-)
Set connection manager of excel as expression
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::AllPathName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"

Add 2 variables one for the excel path and other for the workseet
AllPathName
ExcelWorkSheet

Configure the Excel source as variable name and reference the variable.
Check the image I sent to your mail!!!
If have problems I reproduce this in your project and send you byb email from home!
cheers!!

so? You get it?
:-)
Avatar of hvapro

ASKER

Not yet !
Avatar of hvapro

ASKER

got it!  Thank you so much!!! :)
YES!!!!!!!!!!!!!!
Visit my blog and leave a comment... :-)
www.pedrocgd.blogspot.com
Cheers!
Avatar of hvapro

ASKER

Hi,
In the Microsoft SQL Server Management Studio, I connected to Integration Service and execute my package from there, it works just fine.  But when I try to schedule a job in SQL Server Agent Job.  It failed to execute.

Here's the selelction that I selected in the steps:
Type: SQL server Integration Servicees Package
Run as: SQL agent service account

In General Tab:
Package source: SSIS package store
Server: machine that package is stored
Log on to the server: Use window authentication
Package: path of the package.

Any ideas what is causing the problem?

thanks,



try to put change the package property to dont save sensitive data, and try again!
do that in the control flow properties of your package
Cheers
Avatar of hvapro

ASKER

Here's the error message that I recieved when I deployed the package to the server:

Could not save the package "C:\Documents and Settings\Desktop\Integration Services Project1-2\bin\Deployment\Package.dtsx" to SQL Server "Myservername".  (Package Installation Wizard)

===================================

The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.).  The SQL statement that was issued has failed.


------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)
   at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.SavePackageToSqlServer(WizardInputs wizardInputs, String packagePassword, Boolean bUseSeverEncryption, String serverName, String userName, String password, String packageFilePath, List`1 configFileNames)
   at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.InstallPackagesToSqlServer(WizardInputs wizardInputs)

===================================

The SaveToSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_putpackage', database 'msdb', schema 'dbo'.).  The SQL statement that was issued has failed.


------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToSQLServer(IDTSPackage90 Package, IDTSEvents90 pEvents, String ServerName, String ServerUserName, String ServerPassword)
   at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServer(Package package, IDTSEvents events, String serverName, String serverUserName, String serverPassword)