SQLServerCentral / Data Warehousing / Data Transformation Services (DTS)

Multiple extracts using DTS

ramrajan — Fri, 04 May 2012 00:15:46 GMT

i have stored procedure which returns two results.CREATE PROC usp_sampleAS select * from employeesselect * from DepartmentNow i have to export the two results sets data to two csv files with names employees.csv and department.csv.How i can achieve this fuctionality using DTS in sqlserver 2000.Please help me ................

Implement Metadata driven ETL in SQL SERVER 2008

BJ-594656 — Wed, 15 Apr 2009 00:53:12 GMT

HiI am planning to design Metadata driven ETL implementation using SQL SERVER 2008 SSIS.I need some sample examples or related documents /site references to implement this.Thanks for any ideas, suggestions, or thoughts on this.Regards,BJ

Column order in Transform task not taking effect.

GF — Fri, 20 Apr 2012 00:04:06 GMT

I have a transform task that gets data from an excel file and splits it into two separate text files.I only need some of the columns provided but in the transform or data pump, on the transformations tab, right click on the transformations and select properties. Here is where one is supposed to be able to set the column order. I set my column order and click on test. Everything looks correct. But when I run the task the order is not correct.Anyone have any idea on how I can solve this problem.As usual this has turned into a critical situation for me.ThanksAgain

How to make DTS Start at a certain task.

GF — Thu, 12 Apr 2012 12:40:42 GMT

Greetings,I have a SQL2000 DTS package that populates an Access database.I have several "Execute SQL Tasks" that delete the data from the tables in the Access database.I then have several "Transform Data tasks" that populate the tables in the Access database with the latest data.I have On Success Precedence constraints between the "Execute SQL Tasks" in order to control the sequence of table deletions in the Access database.The problem I am having is the "Transform Data tasks" (Populating Data) appear to be running before or at the same time as the "Execute SQL Tasks" (Deleting of data). The end result is some Access tables are populated and others are not.I tried to correct the issue by rebuilding the package starting with creating the deletion tasks first and then add the population tasks last, but that did not help.I am at a loss as to what to do to fix this problem.Any and all help is appreciated.ThanksGary

SQL Server 2000 Enterprise Manager gets hanged when i do right click-->Propertise of Transform Data Task

am0081174 — Thu, 12 Apr 2012 08:02:57 GMT

Hi all,I have created a DTS Package in SQL Server 2000 to Load data from Teradata Server database to my Local SQL Server 2000. To do this i have created System DSN(Created to access Teradata Server).In DTS package i have taken below components1.Other Connection (In this i mentioned the System DSN Name)2. Microsoft OLE DB Provider for SQL Server3.Transfer data Task (Connected betwn Other Connection & SQL Server)Now to do Transfer data from Source (Other Connection) to destination (SQL Server) i right click on Transfer data Task & then select Propertise.Then my SQL Server Enterprise Manager gets hanged for infinite time. :-(

DTS Error string: The number of failing rows exceeds the maximum specified.

balaji.ganga 68339 — Tue, 10 Apr 2012 01:40:49 GMT

Hi we had export the data from sql to MS-Access. while exporting the data,739612 rows were copied successfully. The very next row does not copied due to the below error.DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)Error string: Error at Destination for Row number 739613. Errors encountered so far in this task: 1.Error source: DTS Data PumpHelp file: Help context: 0Error Detail Records:Error: -2147467259 (80004005); Provider Error: -66257908 (FC0CFC0C)Error string: Database has reached maximum size.Error source: Microsoft JET Database EngineHelp file: Help context: 5003036DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147213206 (8004206A)Error string: The number of failing rows exceeds the maximum specified.First Column is ID field. It was automatically incremented by 1 in the source table.739613 row is missed in the source table. Datatype were different in the first column of both tablessource and destination. { source id int, destination id number}I would appreciate, if anyone help us to resolve this issue.RegardsBalaji G

Cannot delete filename. There has been a sharing violation.

StefanJ — Tue, 13 Jul 2004 10:33:00 GMT

I get the following message after a file has been locked by a long running job (which fired a DTS package) which I had to stop. "Cannot delete filename. There has been a sharing violation.The source or destination file may be in use."Other than reboot the server, is there any other way of deleting this file?

Can DTS lose records?

Richard Warr — Thu, 22 Mar 2012 10:29:12 GMT

I used DTS to import a table from one server (2005) to another (2008) yesterday. Today I've noticed that the source table has 24713 records but my imported table has only 24673. If I do the same thing today I get the full complement.The package reported success and there's no apparent pattern to the missing records (first/last 40 etc.). I'm confident that neither source nor target has been modified since.Is there any precedent for this? I'm being asked to explain where the data went and I'm at a total loss.

DTS in Server 2005 - What is comparable in Server 2008 R2

goofy_1969 — Thu, 15 Mar 2012 09:16:00 GMT

My company recently upgraded to Sequel Server 2008 from 2005. I am used to saving my work as a DTS package and then scheduling the package to be updated nightly. I can't seem to find that on our new server. What is the comparable option in 2008 R2? Thanks.

Error 0xc020901c: Data Flow Task: While Importing data from an Excel File into SQL Server 2005

priyap — Tue, 11 Mar 2008 18:35:01 GMT

While importing data from Excel file into a table in SQL Server 2005, i am getting the following error.- Executing (Error)MessagesError 0xc020901c: Data Flow Task: There was an error with output column "Description" (30) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "Description" (30)" failed because truncation occurred, and the truncation row disposition on "output column "Description" (30)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Risks$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard) Any help much appreciated.Thanks

Getting the name of a file in a location when the file name changes using DTS

GF — Sun, 11 Mar 2012 23:21:56 GMT

To all,Using SQL 2000 DTS.I have an Excel connection named "FileToProcess".I have a global variable named "gvSourceFilePath" and I have assigned something like this to the Global Variable,"Drive Letter:\FolderName\"Any number of files will be dropped off in the location defined by the global variable and they will be named in this format ?????XXXX.xls ????? means I have no idea what this part of the file name will be.XXXX is a 4 digit number that looks something like this 4700 or 2600 or 3300, there will be 33 variations of this number and the file extension is .xlsI am trying to use Active X to accomplish this task.This is the rest of my process so you will have the big picture.Once I get the name of the file, if it is something like ???4700.xls I want to process it by extracting certain columns to text file1 and certain columns to text file2. I then need to move the resulting text files to a location specific to the file name such as 4700, or 2600, or 3100 files. Again, there are 33 variations for the file names so there will be 33 different destinations.Here is what I have as of this time.Function Main() Dim oConn, sFilename ' Filename format - eXXXX.xls sFilename = DTSGlobalVariables("gvSourceFilePath").Value & _ sFilename & ".xls"msgbox sFilename Set oConn = DTSGlobalVariables.Parent.Connections("FileToProcess") oConn.DataSource = sFilename Set oConn = Nothing Main = DTSTaskExecResult_SuccessEnd FunctionI would really appreciate it if someone could help me finish this off.I have beating my head against a wall and I am running out of time.Thanks to allGary

Can i have dynamic mappings in Transform Data Task

vishnu.saxena — Fri, 09 Mar 2012 01:14:43 GMT

Hi All,i am new to DTS, i am facing a problem.I have a flat file (fixed length | (pipe) delimt.) as source file, i need to read the contents of this file into SQL server 2000 table using Transform Data task. My problem is the number of columns in the Flat file are variable (based on the code that record may contain for e.g. if code is 01 then that record may hold 5 col's if code is 06 that record may contain 40 columns) my problem is i am using copy column in Transformation mapping (with maximum fields that the file can accommodate; but when the file does not contains the record having max number of columns the transformation fails.) E.g. if File contains some thing like below01|ABC|TestDummy|DummyRec1|DummyRec206|ABC|TestDummy|DummyRec1|DummyRec2......DummyRec40it worksbut it fails for below01|ABC|TestDummy|DummyRec1|DummyRec205|XYZ|TestDummy|DummyRec1|DummyRec2..| |..DummyRec30(i.e. max field length is of 30 column)i would be greatful if you can provide any help / pointers (w.r.t creating dynamic mapping skipping the error Column not found while transform step)Thanks Vishnu

How to export sql server data into multiple excel sheets ?

deepti811 — Mon, 21 Apr 2008 05:22:25 GMT

[b]How to export sql server data into multiple excel sheets ? [/b]I don't want to do this from DTS or SSIS. i have dalready done this from OPENROWSET, but only single excel sheet, i want to export data on multiple attached excel sheets on per day bases.Pls. any one respond . this is urgent and important for me.thanks. :)

DTS package to call a stored procedure and export the resultset to a excel sheet.

balajiammananna — Mon, 27 Feb 2012 09:24:10 GMT

Hi All,I have a requirement which is as follows:I have a stored procedure under a database. Whenever i execute the procedure it will produce a number of records.What i want is i need to create a DTS package which will call the stored procedure and produce the result set in a excel sheet by taking the excel template from a shared folder.Please help me for this requirement. As i can't implement SSIS in this situation because the databse server is SQL SERVER 2000. Please help me awaiting for your replies.Thanks and RegardsBalaji Y

DTS desinger shows blank page

Nsh — Wed, 22 Feb 2012 23:20:47 GMT

Hi,Recently I was asked to install sql tools for the new BI developers.I did SQL 2005 & SQL 2008 R2 client tools (in this order). Then I was tool they also need sql 2000 client tools so they can open dts packages and compare them with ssis packages that was migrated to make sure no columns/tasks were left out.I installed the sql 2000 client tool + sp4 and when the users try to open the package it give this message:"mmc has detected an error in a snap-in it is recommended that you shut down and restart MMC"Please let me know how I can resolve this. Thanks, Nisha.

DTS package run activity

TeraByteMe — Mon, 20 Feb 2012 14:22:17 GMT

I need to determine when a DTS package was executed and by who? Is this information captured in any of the database/table and if so where can I find them?Thanks,Sean

How to read key value from appsetting in app.config in Script task using vb.net?

Sidd303 — Mon, 20 Feb 2012 05:28:41 GMT

Hi,I am very new to SSIS. I have to create a DTS package which encrpts the username and password written in the xml configuration file and then decrept the same when we run the dts package.Can anyone please help?Thanks in Advance.

DTS package fails in batch job

Brian Brown-204626 — Thu, 04 Oct 2007 08:27:49 GMT

We have a user who is running a group of three DTS packages from a batch scheduler using dtsrun. Two of the packages run ok. The third is getting the following output:DTSRun: Loading...DTSRun: Executing...DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: DTSStep_DTSOlapProcess.Task_1DTSRun OnError: DTSStep_DTSOlapProcess.Task_1, Error = -2146828236 (800A0034) Error string: You were not connected because a duplicate name exists on the network. Go to System in Control Panel to change the computer name and try again. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700Error Detail Records:Error: -2146828236 (800A0034); Provider Error: 0 (0) Error string: You were not connected because a duplicate name exists on the network. Go to System in Control Panel to change the computer name and try again. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Bad file name or number Error source: DTSOlapProcess Help file: Help context: 1000052DTSRun OnFinish: DTSStep_DTSOlapProcess.Task_1DTSRun: Package execution complete.Completed at 04-Oct-07 06:02I have checked and there is not another computer with the same name on our network. I have checked in the package and the connection task is correct and the same as the other two packages. I am open to any other suggestions.

Formatting Date (DTS ActiveX script)

TeraByteMe — Thu, 02 Feb 2012 13:10:36 GMT

I am attempting to format the date to later include in the filename that the script creates. However, this line doesn't seem to work. Format shoudl be as YYYYMMDD. Can anyone tell me what I am doing wrong here?strDate = format(year(date), "yyyy") & format(month(date), "MM") & format(day(date), "dd")Thanks

SQL Server 2008 R2 SSIS Changed Owner Now won't get files from FTP site

Kamels — Mon, 30 Jan 2012 11:19:48 GMT

The SSIS package was originally owned by a developer who has since which left the company.Their window account has been disabled so we changed the owner on the SSIS package to myself and I have full rights to all sites.The package uses a flat file connection manager to access the FTP site and grab the files and then process them downstream.Since changing the owner we have restarted the SSIS package and the SQL Server Agent; however, the job does successfully run without errors but it does not process the files from the FTP site. I can run the same package locally and it performs as expected.Is there some hidden agenda I need to check after changing owners? Or do I need to just re-establish the package under the new owner and disable the original?

Adding a column to a table at import (run) time.

DeanORenO — Fri, 06 Jan 2012 07:09:19 GMT

I have an SSIS package that I used to import excel data, I need to add a column to the table when I import the data, however, the column, is contrived data joined from another table that already exists in the sql server database. Does anybody know how I would even begin to do this? I've tried "derived column" however, the data that populates the column is not derived from the source excel data, rather, a join from the data to that other table.Thanks:-P

Cannot edit DTS Package on Windows 7

john.barry-1031177 — Mon, 09 Jan 2012 09:52:25 GMT

When trying to open a DTS package using SQL Server Management Studio 2005 on Windows 7 (64-bit) get the error:------------------------------[b]This package cannot be edited. SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.If the "SQL Server 2000 DTS Designer Components" download is already installed, there might be a problem with the compatibility of icons persisted in this DTS package. See the following KB article for more details: http://support.microsoft.com/kb/917406 (Microsoft.SqlServer.DtsObjectExplorerUI)[/b]------------------------------Opening a DTS Package works on all of our other operating systems including a Windows 2008 R2 64-bit server, Windows 2008 64-bit, Windows XP. I have tried all that I have found suggested online including: - Installed SQLServer2005_BC_x64.msi (Backward Compatability). - Installed SQLServer2005_DTS.msi. - In the PATH Environment Variable have moved 'C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn' in front of all other SQL Server folders. - Have edited sqlwb.exe.manifest as suggested in http://support.microsoft.com/kb/917406. - Have rebooted.All of this to no avail. Please help.Thanks.John

How to Convert from Varchar to Decimal

Justin Doh — Wed, 05 Oct 2005 11:59:00 GMT

I am asking to see what I am doing wrong.I am trying to Convert or Cast from Varchar to Decimal while I am doing DTS, and am constantly getting this error:

" Error converting data type varchar to numeric."

Currently, I have this data mode:1) FROM Staging table:varchar / length 3

2) TO Production table:decimal / length 5 / Precision 3, Scale 0

I used this syntex to covert such as:INSERT INTO tableB FieldNameBSELECT CAST( FieldNameA AS decimal(3,0))FROM tableA

I know SQL's Book on line mentions as Implicit conversion, and so this is the reason that it should automatically convert, but I don't have any clue

what to do at this moment.

Appreciate for any feedback.

Derived Column Transform using up to 6 OR Statements

trinityr — Thu, 03 Nov 2011 14:45:43 GMT

I have a derived column that I am trying transform based on a range.(lv <= "20") ? "1" : (lv <= "50") ? "2" : (lv <= "80") ? "3" : (lv <= "100") ? "4" : (lv <= "120") ? "5" : (lv <= "150") ? "6" : (lv <= "300") ? "7" : ""Here is what I have so far. All values after the 4, 5, 6, and 7 do not seem to get read properly and are changed to 1 and 2.Would a look up prove to be better due the extended amount of OR statements?

job failed while accessing dts package

balaji.ganga 68339 — Wed, 07 Sep 2011 21:46:33 GMT

Hi,we have a job which runs on daily. the job had failed. Then we got the history details.. Could you please help us to investigate further.MessageExecuted as user: amet\sql. ...rt: DTSStep_DTSExecuteSQLTask_5 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_5 DTSRun OnStart: DTSStep_DTSExecutePackageTask_1 DTSRun OnFinish: DTSStep_DTSExecutePackageTask_1 DTSRun OnStart: DTSStep_DTSExecutePackageTask_2 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_1: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_2: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_17: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2; DTSStep_DTSDataPumpTask_15: 0 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: DTSStep_DTSExecutePackageTask_2... Process Exit Code 1. The step failed.

DTS truncates Excel sheet column while using the template file

jeyis — Thu, 08 Dec 2011 05:52:18 GMT

Hi,I am using dts package to execute a stored procedure and storing the output in a excel file. I was facing a problem in which the next time package runs the data was getting appended on the same file . so i used a template file and i solved this prob. But when I use the template file the data is getting truncated in between. Its truncated with 256 characters which was not happening before.Could any one please throw some light on this to resolve this issue.Thanks in advance.Jeyis

Loop though file on mask

WaitingWonder2 — Wed, 23 Nov 2011 05:57:59 GMT

Hello,I'm using Foreach loop to loop though file based on a mask (CCC*.txt) But I also need to loop through DDD*.csv files as well. How I can do this? I've tried to indicate "CCC*.txt OR DDD*.csv" in Collection.Files property but it is not working.

Cannot lock object 'Lazy processing token'

kamakshi.224 — Thu, 17 Nov 2011 05:33:51 GMT

Hi All,I am gettting below error while processing the cube from DTS Package.Step Error Source: DSOStep Error Description:Cannot lock object 'Lazy processing token'.Error: Connection to the server is lost Verify that the Analysis server service (MSSQLServerOLAPService) is running.Please restart the application.Step Error code: 80040075Step Error Help File:Actaully we have migrated the SQL Server from one location to other location last week. After that this job got abended with above error message.Can any one tell me the why this issue happened and what need to be done in SQL or Analysis server to resolve the issue.Note: Firewall service is desabled in SQL Server.Looking forward for your reply.

View the DTS Package Logs on a SQL Server 2005 Instance

Welsh Corgi — Thu, 10 Nov 2011 13:05:15 GMT

I belive the answer is no but can I View the DTS Package Logs on a SQL Server 2005 Instance?Thank you.

SQL server 7 repointing of destination

someone83 — Thu, 14 Jul 2011 20:09:28 GMT

Dear All,I am doing a repointing of the target table in SQL server to another ODBC destination. The problem is, if i were to specify a new target ODBC destination, i have to do the repointing of each column(in the transformation job) to the new target column. There are about 200 jobs and each tables has many columns.Besides this manual and tedious process, would like to seek your expertise or by experience, is there any other simpler yet automated way to perform this task?

DTS Package fails when scheduled but runs when manually run

Chris Golla — Fri, 21 Jul 2006 12:07:00 GMT

Using SQL Server 2000 Data Transormation Services (DTS), I have created a package which imports data from an external database (Oracle 10G) via the OLE DB Provider for ODBC.

The package executes successfully when run manually (from within the DTS Package Designer or from the DTS Local Packages context menu).The problem arises when the package is executed as a scheduled SQL Server Agent Job. Every time the job is executed, the following error is recorded in the Job History Log:

Code:

Executed as user: AMERICA\SVCSQL. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Specified driver could not be loaded due to system error 1114 (Oracle in OraClient10g_home1). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 160 (A0) Error string: Specified driver could not be loaded due to system error 1114 (Oracle in OraClient10g_home1). Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

The America\SVCSQL account is an admin on the windows server and runs the sql server and agent. I can login manually as America\SVCSQL and successfully run the package.

Does anyone have any suggestions for resolving this problem?

DTSRun Error - manual works, command line doesn't - NOT permissions

SQLSandwiches — Tue, 25 Oct 2011 12:47:28 GMT

Hello,I inherited a package from 2004 that fails every week. It's a real simple job that takes some data from an AS/400 and puts it into SQL. It clears out some data, then transfers data from 3 tables. Nothing complex.It's on a 2005 server (I guess imported at sometime).If I set up the job in SQL to execute the package, it will fail. I do a simple DTSRun /S (local) /E /N "package name" and it fails every time. If I manually open the package and run it, no problem.I've checked permissions and everything is ok there. From the same account that can manually run the package from the editor, I'll open up the command line and get the same failure.I've also set up logging on this package and here is where it gets weird. Here is the error I get:Step Error Source: Microsoft Data Transformation Services (DTS) Data PumpStep Error Description:Column name 'GPPURJ' was not found.Step Error code: 80042008Step Error Help File:sqldts80.hlpStep Error Help Context ID:0So if I run this package from DTSRun, it can't find one column but if I run it from the editor, it can. What's up with that?If I create a new package that checks the same failing table/column, I get the same error ONLY when calling the package from DTSRun.Last note, someone said something about a failover (and failback) a couple months ago.Anyone in SQL land have any ideas?

Importing Access into SQL Server

shanewiso — Wed, 05 Oct 2011 05:10:53 GMT

I have never used DTS... I am looking to important an Access database into a SQL Server warehouse.This Access database has configuration data that can be deleted and re-imported each time...But it also has transaction information that will need to import from the last transaction ID each time.Some basic questions...Can I use SQL to query the structure of Access tables / columns ?Assuming this is possible I could then get the last transaction ID and store this to effectively manage transaction imports.Regards

Importing multiple text files

ahmad-326642 — Fri, 25 Jul 2008 03:52:28 GMT

HiI am fairly new to Sql server. I have a directory where i have all these text filesD20080710.txtD20080711.txtD20080712.txtall these files needs to be imported into sql server . I normally do it either individually or i merge them using copy command in DOS.But I am trying to create a DTS package which loads all these text files automatically. But also insert the filename (D20080710.txt) to table.So that next time when DTS package runs , it checks if the file is already not loaded, based on the filename and if it does not exist it loads thefile and update the table.I will appreciate if any one could help me with this taskThanks

2008 R2 Copy Database Error

smiglewski — Tue, 27 Sep 2011 09:35:05 GMT

I'm replacing the WSBS2003 with SqlServer2005 server with Server2008 SqlServer2008 R2 on a new machine. The machines reside on different domains but are on the same physical network. When I try to use the Copy Database Wizard on 2008 I get the following error in the log. Everywhere I've looked at posts so far say to use the restore utility instead without a resolve to using the Wizard that moves all related objects from the source. Surely MS has thought of how to copy over different domains so any help would be appreciated.[i]OnInformation,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferred server objects to SVR03OnInformation,SVR03,NT AUTHORITY\SYSTEM,CDW_NOIR-FS3_SVR03_1,{3F42E56A-726E-40E7-9F88-F10EAF4A8607},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferred server objects to SVR03OnInformation,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferring database PlantDB1_TESTDB from NOIR-FS3 server as PlantDB1_TESTDB to SVR03 serverOnInformation,SVR03,NT AUTHORITY\SYSTEM,CDW_NOIR-FS3_SVR03_1,{3F42E56A-726E-40E7-9F88-F10EAF4A8607},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferring database PlantDB1_TESTDB from NOIR-FS3 server as PlantDB1_TESTDB to SVR03 serverOnInformation,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Added data file PlantDB1 in path N:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PlantDB1_TESTDB.mdf to file group [PRIMARY]OnInformation,SVR03,NT AUTHORITY\SYSTEM,CDW_NOIR-FS3_SVR03_1,{3F42E56A-726E-40E7-9F88-F10EAF4A8607},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Added data file PlantDB1 in path N:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PlantDB1_TESTDB.mdf to file group [PRIMARY]OnInformation,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Added log file PlantDB1_log in path N:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PlantDB1_TESTDB_log.ldfOnInformation,SVR03,NT AUTHORITY\SYSTEM,CDW_NOIR-FS3_SVR03_1,{3F42E56A-726E-40E7-9F88-F10EAF4A8607},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Added log file PlantDB1_log in path N:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\PlantDB1_TESTDB_log.ldfOnInformation,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferring data to database PlantDB1_TESTDB from PlantDB1_TESTDBOnInformation,SVR03,NT AUTHORITY\SYSTEM,CDW_NOIR-FS3_SVR03_1,{3F42E56A-726E-40E7-9F88-F10EAF4A8607},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:02 AM,9/27/2011 10:24:02 AM,0,0x,Transferring data to database PlantDB1_TESTDB from PlantDB1_TESTDBOnError,SVR03,NT AUTHORITY\SYSTEM,NOIR-FS3_SVR03_Transfer Objects Task,{17A5DD4D-BD8F-4B8A-B062-6583C7BC51D7},{734D719B-3BE4-402E-A3C9-67AFB0BFC08C},9/27/2011 10:24:19 AM,9/27/2011 10:24:19 AM,0,0x,ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_changedbowner @loginame = N'PLANT\Admi..." failed with the following error: "Cannot find the principal 'PLANT\Administrator', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C} [/i]

Import .DTS file into an SQL 2000 server

balasach82 — Sat, 25 Jun 2011 20:38:37 GMT

I made changes to an Production DTS and need to import that dts file into prod system now with the same name. I tried to save it from the DEV system (where i made the modifications) using "Save As" and then choosing the Prod sql server. But it failed that the pkg already exists with different Id. So, i saved it as a .dts file and tried to import into the prod. It also failed with the same error. How should I resolve this?

Is it possible to get the package name,server name,username etc in dts package using tsql script

balaji.ganga 68339 — Thu, 11 Aug 2011 07:07:23 GMT

Hi ,We have around 450 dts packages. I need to findout what are all dts packages were connected in scs database. I cannot open each and every dts packages and see the connections. Instead of thatIs there any way to get the below details using tsql script.Example :dtspackagename, servername,databasename,username etc..I would appreciate, if anybody share the script.Thanks in advance.Balaji G

2008 DTS question! stardds.dll File version: 2000.80.2151.0

mdduncan — Wed, 06 Jul 2011 15:41:46 GMT

I'm unable to locate a copy of the stardds.dll File version: 2000.80.2151.0 so that I can edit DTS in 2008 SSMS. I checked each Service Pack 1-4 and have searched the internetfor this specific version but to no avail. Any help would be great.

Problem in updating the global varibles through Activex script in DTS Package

progzr — Wed, 27 Jul 2011 12:04:21 GMT

I have a dts package in Sql server 2000 which has three global variables in it. The script run as expected when i run it manually. But when i close the dts package and then run it by right clicking it or when i schedule the package.. the variables wont update. I have checked the permission and security of sql server agent and the user.. from which i run the script.. all admin rights.Please help...Below is the script'**********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main()Main = DTSTaskExecResult_SuccessRowDate = DTSGlobalVariables("RowDate").valueStartTime = DTSGlobalVariables("StartTime").valueInterval = DTSGlobalVariables("Interval").valueIF StartTime = 2400 ThenStartTime = 0RowDate = DateAdd("d" , 1 , RowDate)ElseIF Interval = 30 ThenStartTime = StartTime + 30Interval = 70ElseIF Interval = 70 ThenStartTime = StartTime + 70Interval = 30End IFDTSGlobalVariables("RowDate").value = RowDateDTSGlobalVariables("StartTime").value = StartTimeDTSGlobalVariables("Interval").value = IntervalEnd Function

Urgent reply Preventing SSIS packages from running across servers when executed

pespes009 — Fri, 08 Jul 2011 08:48:41 GMT

Hello,I am looking for a script or event handler that will prevent SSIS package I created from running across servers when executed on other servers when executedI am looking at a pre execute event handler to do this...Any ideas please..urgent replyRegards