DTS Packages (.dtsx) And Automation Through C#

Jul 23, 2007

I have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.

View 1 Replies


ADVERTISEMENT

Upgrading System To Run DTSX Packages Instead Of DTS Packages

Aug 2, 2006

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!

View 1 Replies View Related

How To Debug DTSX Packages?

Aug 1, 2007

I have some familiarity with DTS packages, and find them easy ( generally) to debug as you can run them step by step and simply find the offending step. when they fail they also put a message box with the error in the centre of the screen. My system has recently been updated and now uses DTSX packages. I can open and veiw these in visual studio, but i cannot run them one step at a time, and they dont give a nice error message when they fail. how do you debug a DTSX package, or at least make debugging it a little easier?

View 7 Replies View Related

DTSX From Deployed Packages

Dec 14, 2007

Hi,

I want to take the backup of all SSIS packages deployed in the SQL server. I need the backup in DTSX format, how should i do that. Please advice.

View 8 Replies View Related

Post Dtsx Packages To Forum

Mar 30, 2007

First off, can you post .dtsx packages to the forum? I always end up cutting and pasting code, which is by is nature incomplete, as it generally has external dependencies (connections, variables).

Perhaps you cannot do so for security reasons?

Second, is there a good blog site that allows you to post .dtsx packages?

I'd rather post .dtsx links than cut and paste code (which half the time formats the code un-recognizably), or even worse, post package xml, which contains the layout information and possibly IL that makes it all but unreadable.

View 4 Replies View Related

Creating A Package Of Dtsx Packages

Jan 23, 2007

Hi



I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?



Thanks.

Robin

View 17 Replies View Related

Errors With SQL Cluster Running .DTSX Packages

Sep 25, 2007

I'm experiencing some frustration with my active/passive SQL cluster not running my .DTSX packages. I am hoping someone can shed some light on what I need to do.


I've created some .DTSX packages with the SQL Server Business Intel Dev Studio. I initially built & tested these packages in a non-SQL cluster environment without any problems. I'm now re-creating them to work on our SQL cluster. If I run the package through the Dev Studio it works great.

The packages basically grab .txt files from one of the shared drives (which is a resource of the sql cluster group) and imports the data into the one of the databases. The database does not have any special settings (right-click -> new database... -> enter name -> click ok).


I've setup a SQL Server Agent Job with 1 step with the following properties.
Step name: I Offices
Type: SQL Server Integration Services Package
Run as: SQL Agent Service Account
Package source: File system
Package: G:ImportRAGFLOffices.dtsx


When I run the SQL Server Agent Job through MSSQL Server Management Studio (right-click -> start job) I get an error on "Execute job 'RAGFL TestJob'".


These are the 2 messages that show up when I view the history of the SQL Server Agent job.

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 0
Server BADBOYS
Job Name RAGFL TestJob
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (I Offices).
***********************************************************************************************

***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)

Step ID 1
Server BADBOYS
Job Name RAGFL TestJob
Step Name I Offices
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: THEISLANDAdministrator. The package execution failed. The step failed.
***********************************************************************************************

THEISLAND is our internal domain.

View 2 Replies View Related

Best Method For Running/Executing Multiple DTSX Packages?

May 13, 2008

Hello all -

Based on advice from this board, I am setting up our newly-converted DTSX packages (sql server 2000 (DTS) to 2005 (SSIS) package conversion) to utilize package configurations to make it easier to run them in multiple environments (dev, testing, production, etc...). This seems to be working just fine, but I now have another question.

We have approximately 12 DTSX packages which need to be executed in a specific sequential order. I was wondering what the best / most widely accepted method is to allow for running them as an automated process. If you double click on each DTSX file individually, the "Execute Package Utility" pops up, and you can run the package. I'd like to know the best way for us to be able to run each package, one after the other (each one waiting until the previous one completes before it begins), until they have all processed. It would be nice if it did not rely on SQL Server being installed on the machine on which it is being run...

thanks for all the fantastic support on this site

View 6 Replies View Related

DTSX Package Calling Another DTSX Package Question

Jun 13, 2007

I have a dtsx package that is calling another dtsx package, however, if the called upon dtsx package fails with errors or what not, then the calling package does not continue as well. Is there any way to override this such that if the called upon package fails, the downstream actions in that package can stop, but the calling packages downstream actions to continue?

View 3 Replies View Related

Automation

Dec 1, 2007

 Is there a way to automation SQL on an SQL Server to perform
impports and exports at a certain of the night.  The server is
always running. Thank Dee 

View 6 Replies View Related

DTS Automation

Feb 5, 2001

I'm trying to bring down a file from the AS/400 to update a SQL database table. Either thru the configuration limitations on the SQL server or ??? I cannot reach the as/400 from my SQL box so I'm running the data down to a Citrix server to a text file via client access.
I need to automate the DTS package run on the SQL box to pull the data from the Citrix server over to the SQL box.
How do I set up the DTS package to run on a schedule?

View 2 Replies View Related

Ole Automation.

Apr 30, 2008

One of my developers needs on enable Ole Automation on production? What security loop holes we are creating, by enabling this feature in SQL2K5?



------------------------
I think, therefore I am - Rene Descartes

View 5 Replies View Related

Automation

Dec 3, 2007



How can I import an xml file to SQL 2005 at the same time every night? I
will need to create a new database first via the import after that I will be
appending to the dataabase. Then I will need to xport the data into a
different xml file and upload to my hosting system which is yahoo. All so the
original xml file is on the yahoo. store server.

I have SQL 2005 standard install on my personal work stations. When I iron
the bugs out I will be running this from our SQl 2005 Enterprise server.


Thank you
Dee

View 11 Replies View Related

Automation Error ?

Mar 18, 2001

We've an a VB application connecting to SQL Server 7. Recently the log files show 'Automation error' in one of the routine. I've scan thru the code & pass in some test case & it is ok.

Any idea what could have cause the 'Automation error'?

Thanks in advance.
Ben

View 2 Replies View Related

Automation On SQL Server

Nov 30, 2004

Hello Experts,

I am a New Bee in SQL.I would like to know,As a System Admin I would like to automate the task of keeping the SQL Server up to date with latest Service Packs and Patches.
Is there any procedure / Software where I can automatically download the patch / sp's from the site and install on the servers or maybe schedule the task for every month.

I would appreciate a reply from the experts

Best Regards

SunilVKulkarni

View 1 Replies View Related

DR Scripting Automation

Jul 9, 2007

Hi,

I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).

Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.

Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"

Regards

Mark

View 15 Replies View Related

Excel Automation

Jul 20, 2005

Hi,I tried to add column headers and delete all rows to excel databasewhich is a linked server to my sql server db.I got an error message, "delete data through linked server is notsupported by Isam".I tried to use FSO to write to the excel file, but seems like FSOsupport write to only text file.the last time I tried to create an excel applicaiton object and temperwith its cell, I hang my Excel application and had to reinstall it.Have any ideas,Thank you very much

View 1 Replies View Related

Automation Error

Feb 2, 2007

Hello,

Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.

Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!

View 2 Replies View Related

SSIS And OLE Automation

Nov 17, 2006

In MSS 2000, it was possible to have a stored procedure that used OLE Automation to execute a DTS package. E.g.

declare @oPKG int

exec sp_OACreate 'DTS.Package', @oPKG OUTPUT

declare @pkgName varchar(128)

declare @server varchar(20)

declare @cmd varchar(1100)

declare @userid varchar(20)

declare @ServerPWD varchar(20)

set @pkgName = 'PKTestOuter'

set @Server='myServer'

set @userid='myuser'

set @ServerPWD='myPW'

SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @userid + '", "' + @ServerPWD + '", 0, , , , "' + @PkgName + '")'

EXEC sp_OAMethod @oPKG, @Cmd,NULL

exec sp_OAMethod @oPKG, 'Execute'

When I move - but not migrate - packages to 2005, this still works. I don't have a problem with permissions or any of the other problems that I have seen posted in this forum. But if I migrate a package to SSIS, the package is now seen in sysdtspackages90 instead of table sysdtspackages (assuming I remove the old version). But "LoadFromSQLServer(...)" can't find it anymore. So it seems like one of the following is true:

a) There is a different item that I should use in sp_oacreate besides DTS.Package. I tried SSIS.Package or DTS.Package90 which seemed like reasonable possibilities but they don't work. Is there something that will work? Or is "LoadFromSQLServer(...)" not supported for SSIS packages?

b) Or OLE Automation doesn't work with SSIS packages. OLE Automation is enabled and it works on non-migrated packages. Is it true that it was decided by Microsoft that this capability was not needed in SSIS?

I see numerous postings saying not to use the sp_OA routines. I understand there are other ways to execute an SSIS package from a SP and that there may be more secure ways. But I need to know if it is an absolute requirement to use these other methods.

Paul

View 1 Replies View Related

Report Automation

Jun 18, 2007

hi



i have created a rss script for report deployment which i working pretty good but icheckout my all reports on report server and from another machine i mapped that drive from command line and run rss script then i got following error

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

:Automation>rs -i PromotionAutomation.rss -s http://fsaleem/reportserver -v en
v=Dev -v DBServer=fsaleem

Unhandled Exception: System.TypeInitializationException: The type initializer fo
r "Microsoft.ReportingServices.ScriptHost.ScriptHost" threw an exception. ---> S
ystem.Security.SecurityException: Request for the permission of type System.Secu
rity.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral
, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet granted
Set, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT
oken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken permToken,
CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32
unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, S
tackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Reflection.Assembly.VerifyCodeBaseDiscovery(String codeBase)
at System.Reflection.Assembly.GetName(Boolean copiedName)
at System.Reflection.Assembly.GetName()
at Microsoft.ReportingServices.ScriptHost.ScriptHost..cctor()

The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Vers
ion=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PathDiscovery="Z:AutomationRS.exe"/>

--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.Main(String[] args)



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





View 1 Replies View Related

PDF Creation Automation

Jan 11, 2007

hello

is it possible to generate PDF files from reports programmatically ?

View 7 Replies View Related

Stored Function Automation

Aug 16, 2006

Hey,How can I schedule a stored function to run atleast once a day, other than by having it as a scheduled task on the server?Sql Server 2000Thanks

View 3 Replies View Related

COM Automation Procs Failure

Oct 24, 2000

I have been trying to execute the following code:

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
--Instantiate a Network Object
EXEC @hr = sp_OACreate 'WScript.Network', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a property using an output parameter.
--EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT
EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--PRINT @property

-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END

I get the same behavior stated in microsoft's support site related to BUG
55840 for Sql Server 7.0.
I get an error message when trying to retrieve with @property OUT parameter.
And I get an Empty record set when NO Output param is supplied.

http://support.microsoft.com/support/kb/articles/Q236/4/40.ASP

I am running Sql Server 7.0 SP2 on Windows 2000 AS.
SP2 has this BUG listed as being Fixed. Am I missing something?

Thanks in Advance for your Help,
Chris

View 3 Replies View Related

DTS File To Table Automation

Apr 8, 2002

Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

View 11 Replies View Related

Backup Method Automation Job

Sep 18, 2007

Hello everybody ,

I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.

can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ......analysis services 2005 only

Thanks in advance
Yukon DBA

View 1 Replies View Related

Replicate A Database Automation

Jul 20, 2005

Hopeing this is something I can accomplish. I use MS-SQL for close to100 CMS web servers. Everytime I create a new website I have to createa new database and then use the export from SQL to SQL option and copyall data / SP's / etc... Just no security information. My question iscan I automate this? Can I create a script that will run all this?Thanks for any help, and thanks for reading.Chris AuerJoin Bytes!

View 2 Replies View Related

Automation Of Running Queries

Jul 20, 2005

I have written quite a complex query in Query Analyser, now need toknow how to automate the running of the query to dump the results intoa flat file at scheduled times. Am an experienced Unix user, where itwould be quite simple but new to sql server, is there a relativelysimple way to do this?

View 1 Replies View Related

Reports Automation -Urgent

Mar 17, 2008

can any one send me a useful link which is easy to undersatnd Step by Step for Aumaotion of Reports Deployment?

View 4 Replies View Related

Access-like Automation In SQL Server 2005?

Jul 8, 2006

I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.

View 1 Replies View Related

Automation Tool For Migrating From Dev To Prod

Jan 16, 2008

Hello,

I have recently become a release manager for SRSS in our company.
Since then I've been swamped with requests to migrate reports, permissions and subscription lists from development environment to production.

Each time I have to do it manually with a lot of clicks. It is a real pain...

So, may be... may be there is an automation tool out there to help me? Does anybody know?

This tool or s/w package should move a report file along with its permissions and subscription lists from one server to another.

Please advise!

thanks,

View 1 Replies View Related

Automation Of Excel To SQL Server 2005

Aug 11, 2007



Hi all,

I have a task which needs to be automated.

1) Excel spreadsheet load into sql server database as a table.
2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.
3) Then, output should be back into excel spreadsheet.

All these steps 1,2,3 need to be automated.

Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.

Thanks for all your help..





View 1 Replies View Related

DB Design :: Backup And Restore Automation

Jul 1, 2015

I need to perform a backup from prod and restore the backup onto Staging server.

This entire task needs to be automated using sqlcmd and staging server is configured with always-on.

What is the best possible way to automate the entire tasks?

View 4 Replies View Related

Client Installation &&amp; Configuration Automation

Jul 16, 2007

Hello!



I am looking to automate the rollout of the SQL Native client to our workstations. What I would like to do is use our login script to initiate an unattended install on the workstation. I will create the unattended file using the one that comes with SQL Server 2005 as a guide. This seems pretty straightforward.



More importantly, the other task I want to tackle is adding server aliases to the workstations. I am thinking of creating an alias for each of our applications and pushing the changes out by merging a registry key(maybe WMI? I dont know...). This way, should we need to move application databases to a different instance, we can just push an updated list of aliases to the workstations. The actual storage location remains transparent to the applications and users, (ie no configuration changes).



Has anyone done this or have any ideas? Are there any pros/cons that I have not thought of?



Thanks for any input!



Chris G

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved