We have a folder in SSIS called ETL and there are about 25 SSIS packages in it.
Now we need to update it.
I tried to delete the folder but you get the message that the folder is not empty.
So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work.
Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes.
But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?
Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
I need to delete multiple sql logins on one of my database servers that are no longer needed. Most of these logins are not attached to any database on the server because the database has been deleted from this server. Is there any way to delete logins that are no longer needed without having to review every login to verify it is not attached to a database before deleting?
I have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user. DELETE FROM T1,T2,T3 WHERE column1='1' how do i do it.
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=- Matt Torbin President Center City Philadelphia Macintosh Users Group http://www.ccpmug.org/
I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.
Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?
I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.
The problem I have is within SQL Server Management studio experess. I have a table called OriginalDrawings that looks like this
CREATE TABLE [dbo].[OriginalDrawings](
[DrawingID] [int] IDENTITY(1,1) NOT NULL,
[OriginalFileName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[PartNumber] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I've loaded it with 40,000 records from my test app. If I open it in SSMSE hit Ctrl-A to select all and then press delete SSMSE appears to hang - it freezes for 10 mins+ (after which I restart my PC. I can delete 1000 records at a time OK, I can delete all quite quickly from my test app by walking through the recordset and deleting each record.
Does anyone know why it appears to hang when I try the delete all?
Thanks
Alec
SQL Server 2005 Express 9.00.3042.00
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 6.0.2900.2180 Microsoft .NET Framework 2.0.50727.42 Operating System 5.1.2600
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'. The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
Hi, I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID]. I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03. I have come up with something like that but it does not seems to be correct. CREATE PROCEDURE [sp_delete_test01_1] (@id [int]) AS DELETE [test01] DELETE [test02] DELETE [test03] WHERE ( [id] = @id)GO Your advise please. Many Thanks.
I have several independant DTS packages that I would like to schedule and run as 1 job stream. (sql2000) I can schedule them individually, but I would prefer to have each one be a step of 1 big scheduled job.
I have a schedule job that errors out on the first step when I attempt to run it. There are 3 packages that I created and I can run them all manually just fine.
I get this error when I try to run the job: -----------------------------------------------
An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80... The package execution fa... The step failed.,00:00:01,0,0,,,,0
I have a rather dumb question, it appears that we can have more than 1 package (.dtsx) in a solution. If I have multiple packages within the same solution, how do I invoke them from the main package in the solution? Thanks in Advance.
I'm having serious problems with the IDE for SSIS for projects that contain more than 5 packages. Especially if these packages call each other with a run package task thats configured with a file connection. Especially annoying are the 20+ "Document contains one or more extremely long lines of text.." messages that pop up during loading / validating. For my project with around 30 packages it takes me around 10 minutes to click through all these pop ups in addition to the long loading time.
Anyone got any tips on this specifically or how to improve performance in the IDE in general? As it is now, the product is a REAL pain to work with for large projects.
Hi Guys and Gals. Thanx in advance for any help or input. I have multiple DTS Packages that run each nite on a Server. The packages import data from SQL Tables on another Server. All of a sudden, the Jobs have started failing. There is nothing in the SQL Logs. The DTS Package Logs (that I save to the Server) specify an unspecified DTS error. Any suggestions? Even better - any suggestions what/where to look to find the cause of these errors? I re-run the Jobs in the morning when I get in and they complete without any problems. If you need more information just let me know. Thanx. billy
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package, I am getting the below error: Error: Error 0xC001000A while preparing to load the package. The specified package could not be loaded from the SQL Server database. .
I would like to use one configuration file for multiple SSIS packages. How I want to do this is to save the connection string of my production server with the initial database field excluded and set that per package file. I am able to read in the configuration file into 2 different packages without issue but I cannot see how to extract the connection string into a ODBC Database Connection object in order to set the initial database. Is there some way I am not thinking of to do this, either through the designer or programatically?
Thanks for any help you all can provide! I am so n00b to SSIS.
I built 3 diffrent packages and i want to be executed in order. The first one is exporting some tables to another database and the other two packages are based on the database built in the first package.
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package, I am getting the below error: Error: Error 0xC001000A while preparing to load the package. The specified package could not be loaded from the SQL Server database. . Then I did the following: 1. Right click on MSDB 2. Import package Inside Import package window: Package location: SQL Server Server: servername authentication: windows authen Package path: \serverC$folder1SSIS Packagesfolder2 Import package as: Package name: package.dtsx Below is the error I am getting: TITLE: Import Package ------------------------------ Cannot find folder "\serverC$folder1SSIS Packagesfolder2". Can I use Execute package task for this purpose?? Thanks!!
I have created several DTS Packages those are working fine, tested and verified. I need to run them by a single click, either by batchfile or some sort of command.
I have several packages that all use the same DTSConfig file to map the OLEDB connections. This works fine in the development environment, however when i run a rebuild with Deployment set to true i get the error
Error 75 System.ApplicationException: Could not copy file "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransformPackagePath.dtsConfig" to the deployment utility output directory "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeployment". ---> System.IO.IOException: The file 'C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeploymentPackagePath.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow)
The DTSConfig file is included in the project under Misc in the development studio as all developers need to have access to the same config file.
The net result is that the MSI deployment package doesnt get built so we cannot do an automated delivery of the data.
Is there a work round or a setting that i can implement to remove the error?
I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.
The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.
My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.
My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.
If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!
My current environment has multiple packages stored in SQL server (MSDB). When working on a set of packages I want to bring them into my local development area Add existing package only allows you to pull one package at a time - anyone have the secret to selecting multiples
I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.
Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.
I've read that a single config file can be used across multiple packages, but am unsure if it applies to what I'm trying to achieve. As an example, I have 2 packages, PackageA and PackageB. Each package has a connection manager defined - ManagerA and ManagerB and I want a single package configuration file to contain the connection string for each manager.
I can define the package configuration of PackageA with no problems. I choose XML file, define the filename and add the connection string to the config.
I then open PackageB Package Configuration and point it to the existing config file, IS tells me the file already exists and allows me to choose the option to "Reuse Existing". I then supply a configuration name and the wizard finishes, without letting me expose any properties. At this point, the configuration file still contains config details from PackageA.
If I then choose to Edit the package configuration in PackageB, I get a warning saying the config values in the config file (relating to PackageA) cannot be found, and the wizard continues. The subsequent PackageB properties which I add overwrite the existing PackageA properties.
So it would appear that what I'm trying to do cannot be done, and I need a separate configuration file for each package?
