Hi
Can we compile the stored procedures as a package in SQL SERVER as we do it oracle and can anyone tell me how to encrypt the stored procedure so that no
one can see the source code.I know with encryption option which doen't show the stored procedures in the syscomments table.
Thanks
VENU
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
Can you use multiple tables in a DTS job?
I copy a large data table from one database to another every night, but I only want to copy the data since the last time the DTS job ran. I am going to create new job that writes the date of the update to a date table.
I then want the DTS job to compare the invoice date from the datawarehouse table to the last date in the Date table, and only write the data with a newer date.
I know the script if the values were in the same table - but how do I use two different tables?
Is there a query or sp I can run to get me all package names on a server?
View 1 Replies View RelatedI need write a code to delete DTS Packages. I'm try using the SQL-NS Objects, but not work's fine. To work first i need execute the package and after delete. Exist another method to do this task??
View 1 Replies View RelatedI have created a dts package on a server using the Data transformation services local package interface. I need to move this package to another server running SQL 7 but I cannot figure out how to move this package. I tried the Import/Export wizard but no luck with (license errors). any ideas from anyone? Thanks
ss
What is DTS guid?
I am actually told to move the DTS package from PreProd environment(Server A) to Production (Server B). Once the DTS packages are migrated then I have to email the DTS guid. I am not sure what I should write in the email. LOL
Urgent help is required. Thanks
Does anyone know of a way to copy DTS packages from one server to another? Thanks!
View 1 Replies View RelatedIm trying to get a DTS package to run a command line but where ever I put the command nothing is executed.
URL
Im entering the command into 'Parameters' area but when the command line opens the command is not passed into it
have a SQL packages which has hundreds of similar small connections..what are they?
=============================
http://www.sqlserverstudy.com
Hi all,I am creating SSIS packages which has flat files as data source and destination is SQLServer2005.I have successfully created packages for around 100 files. In future if the server is changed or/and the same flat file is moved to another location i cannot execute this created packages. I have to redo my work or i have to edit my packages which is a tedious job.Totally i am having around 750 files and each should have corresponding packages. Is there any better way to handle this changes.
Ummm.... else i put it in another way. Is it possible to create a Front end application in which the user gives the Server name,user name,password and the physical location of the flat files and when submitting this, SSIS package has to be created for the flat file which the user has given.
I browsed the net and found a link where they have given a way to create SSIS package using a program. Can anyone explain in detail about this.http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Thanks in Advance.
Sarvan
Hi,
If to convert the DTS packages into SSIS packages what steps we have to follow?
Thanks
Hi Bob,
Please send me some related links for testing
Regards,
raj
We have a development environment where there are 4 development servers and a single production server.
some of our processes rely of some complex DTS packages.
what is the perferred method to create copies of these DTS packages so that they can be put onto all the development systems ?
Hi, I'm building a portal for supervisors can view their agents errors. We have a team that have been using Access to import data and QC it. I want to get these records into SQL but at the same time, the agents need to import their own work. I've create a DTS package that will import the data into the SQL table from a folder the agents have access to. I tried to create the strored proc to be run from the ASP page however it's not working and I'm having a hard time figuring out why. Here is the procCREATE PROCEDURE DTS
AS
exec master..xp_cmdshell 'dtsrun /KEMTSQL02 /ADJUSTMENTS /E'GODoes anyone know what I'm doing wrong? Any help would be great. Thanks!
i have an application that runs on sql server, that is all working fine on the development environment, and nearly ready for deployment in production.
a large part of this application is based on complex dts packages.
is there a straightforward way to migrate all of these packages AND change all the connections to point to the live box rather than the development box, without painstakingly changing all the connection objects (in around 100 packages!!)?
Does anyone know how DTS packages are backed up. Does peforming a DB backup also backup the DTS packages ? Can they be scripted ?
many thanks
I am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
View 2 Replies View RelatedIs there a way that I can move DTS packages from test machine to production machine? Or do I have to recreate new packages in production.
Thanks,
Jim
I am obviously new to creating DTS packages, and I am trying to create a new package. I looked in BOL for DTS samples and was able to install them from the CD. Now I have several DTS files on my hard drive. How can I open them in SQL Designer, or get them to show up in Enterprsie Manager? Any help would be appreciated. I really need help with DTS Designer basics, but cannot find anything.
Thanks,
Jerry
Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
View 2 Replies View RelatedDoes anyone know how we can backup DTS packages?
View 2 Replies View RelatedI am building a new server and I have got the databases from the old server imported. I would like to import the dts packages so I don't have to re-create them. Does anyone know an easy way to do this?
View 2 Replies View RelatedHi,
Here is my problem :
I have around 100 DTS packages on my SQL 7 server. All the packages contain an OLEDB connection with our Oracle 8 Database because i upload some data from Oracle to SQL Server. My problem is that the Oracle DBA have decide to change the password to access the Oracle Database. Is it possible to written a script that will update the password of all the connection include in all the 100 DTS Packages or if I have to update them manually one by one??
Martin
I have seen lots of messages on scheduling DTS packages, but none have solved my specific problem.
I could manually run my local DTS package, but it wouldn't run when I scheduled it. The problem was with connecting using OLE DB provider. I checked everything: my NT account had all relevant permissions, as did the SQL Server agent account. I tried playing around with the job owner.
In the end, the only thing that worked was if I physically created the local DTS package on the Server rather than sitting at my workstation (still under my NT account though - I even logged on to the server with my NT account). The job then ran fine.
I think it is something to do with NT authentication (as I never got the problem before using NT auth). Can anyone shed any light on it? Is it something to do with the fact that with NT authentication the workstation ID is an advanced property of the OLE DB connection?
I have several Repository DTS packages on my server, but these are not available on all client machines. I have no problems working on these packages from my workstation, or some of my coworkers, but others cannot even see the packages. In fact, when logged onto the server itself, the packages don't show up!!
Has anyone seen this, and is there a fix?
Thanks,
Patrick
Why is it that I can run my DTS package locally howeverv when I try to run it as a Job it always fails? I do realize that the sqladmin account is used to run the job and it has all the permissions needed.
Any suggestions?
Also if I kick the job off from my local system it states it cannot find the batch file that I am trying to run? Im running it on the server however it treats it like im running it locally?
Very frustrated..
Thanks
David
Can someone tell me where DTS Packages are saved by default? Is it the MSSQL7/Binn directory?
Thank you,
Michelle Turner
I have 14 DTS packages with same connection name in all of them.
each DTS has atleast 7 transformation task.
Is there anyway I can merge all the DTS packages to one single DTS package?
:confused:
We have several Scheduled overnight runs that include DTS packages. Every morning one or more jobs failed because the DTS packages failed either to start or to finish. I have looked through all the log files and different sources but none give an indication as to what the problem might be or a possible solution.
Please help.
Thanks
Gretl
To make the packages portable I have made connections as (local). Because once the packages go from say Development to Production, they would still work without re-designing them on a different box. The '(local)' as a the servername always looks for a the (local) server its on. But this seems too simple.
I have tired different methods such as using a INI file, Dynamic Protperties task etc., I have used global variables in the packages. Ultimately, I have used configuration tables to supply all the global variables, file names, etc.
Is there a flaw in this idea? Has anyone tired this before? If not how have you made DTS packages portable? Any ideas?
Thanks in advance.