I'm having an issue with a DTS package that I created. It's running on a Windows 2000 Server, XEON processor, 2.5 GB of RAM, SQL 2K with service pack 3. The destination database in is simple mode. The package consists of two Microsoft OLE DB Provider for SQL Server connections. Both conne...more >>

I have a server table all of my servers listed overt there, I would like to retrive server name from that table connect to the server querry about the database sizes and put in to the historical table. How i can loop through each server to get the information dynamically. Basically i want to...more >>

We're in the process of upgrading our DTS packages to SSIS. Some of them have ActiveX scripts. Seems the SQL Server 2005 scripting language is Visual Studio for Applications. But I've also read that MS has already killed this language, see: http://blogs.msdn.com/dr._ex/archive/2005/10/03/47643...more >>

Couple questions on DDQs: 1) Is there any way to (within the ActiveX logic) never have to call or return to one of the "official" DDQ queries and instead call a series of Lookups that Insert/Update/Delete multiple tables? 2) Is there anyway to Begin/Rollback/Commit transactions 1 step at a ...more >>

Hi, I try to setup in ssis importing progress database to sql2005. the progress is connected through odbc. in past, I used dts and worked great. in ssis, when I run export and import wizard, I can't see the odbc source... please help!! AL -- ...more >>

Hi, I'm working on a group of SSIS packages at the moment and I'd like to come up with a way to perform logging on certain events within each package. Each package should log the same information for the same event. Rather than duplicate this logging in each package I want to create a separ...more >>

Hi Champs, Is it possible to somehow use an web service as an input source in SSIS, and if so how to? Many thanks Kurlan ...more >>

Hi, I have created a package which transfers some table contents from a SQL Server database to an Access counterpart. An "Execute SQL Task" deletes the already existing data from the target tables and then, after the succesful completion of the operation, the transfer occurs. Unfortunately,...more >>

Hello to everybody I'm using SQL Server 2000 (sp3) I created a package on the server which uses a global variable . When I run the package( from DTS Designer) it takes almost 1 minute to return 6 rows . I went to take a look at an execution plan and made sure that an optimizer used INDEX...more >>

This is why I never recommend to anyone to upgrade to the first release of Microsoft's products. The "Microsoft SQL Server 2000 DTS Designer Components" (SQLServer2005_DTS.msi - 5083 KB) that is included in the Nov 2005 Feature Pack for SQL Server 2005 does not work as documented. Theoreti...more >>

Hi, I want to setup a task to run about 50 small ssis packages. What's the easy way to do so? I can create a new job and specific one package at a job but that seems a lil' tedious. Is that the only way? I'm new w/ SQL in general btw. Thanks! ...more >>

Moving data from a Sybase (source) database to a SQLServer (destination) database. Some of the inserts are failing and I have been able to get the following kind of info on the offending row(s). Description: "The Bulk Load failed. Unexpected NULL value in data file row 22 column 3. The de...more >>

My IT manager wants me to regularly save all of our DTS packages as Visual Basic files and regularly check them into SourceSafe as they are changed. Is there a *programmatic* way to save a SQL 2000 DTS package as a VB file? I would hate to constantly be having to manually open up packages an...more >>

We have a very simple DTS package to import data from a Paradox 4.x database into SQL server. When the package is executed directly it works fine. When it is run as an sql server agent job it errors with "Microsoft JET Database Engine" reporting "External table is not in the expected format." ...more >>

Is it possible? I'm putting this into 1 lookup: INSERT INTO tblOrganization (iOrganizationId,sIntegrationCode, iOrganizationTypeId,sName,sNameLegal,sCustomFieldString1, srtCustomFieldSmallInt1,sABCLicense,sPhone,iOrganizationStatusId) values ( ?, dbo.padstring(?,6), ?, dbo.in...more >>

Hi. I have a datapump task, and I need my dts to ignore any error and continue working. I just want my DTS to insert the registries it can, and don't fail if it finds a registry it can't insert (obviously, I'm not asking it to insert those wrong registries..) Is it possible? ...more >>

Hi There, I have around 10 DTS jobs which run every night. These jobs basically copy data from views of a source DB to tables in a target DB. These jobs have been failing quite frequently in the recent past. I have checked and there are no contraint violations (like primary keys) in the targe...more >>

Hello all. I imported (not migrated) a few SQL 2000 DTS packages into SQL 2005. Of course, they appear under the Management...Legacy tree. I can't seem to figure out how to schedule these packages using SQL Agent. Migrating the packages to native SQL 2005 isn't an option, because I lost my...more >>

I used wizard to port a DTS script to SSIS, it says that it completed. I only saved it to the database not to a file. In studio manager under mdsb database I can see that the package exist. But I want to edit the package in BIDS to see how complete the porting was, to see if I need to repro...more >>

Hi I have an excel spreadsheet that has 6000 records hardcoded values and the rest are dynamic links to another spreadsheet that update automatically. I used import/export wizard to create a DTS package to import data from this spreadsheet into a table in sql server that has not null constrai...more >>

Hi all, I have a small SSIS package which reads an Excel file and insert data in a DB. Right now I am using Excel connection manager to read from my Excel file as the file is located on the local hard drive. In the future, the Excel file will be located on a web server. How can i get th...more >>

While importing data from excel sheet using OPENROWSET query it converts phone column/field as numeric and format it as scientific notation e.g. "2.55E99" in SQL Server records even if the phone field in SQL Server is varchar and I have already tried changing its format in Excel as Text. Ex...more >>

Hello. I have a DTS package that imports a text file into a SQL 2000 table. When I tested the package I used a file that's 64 MB. I then used a live file that's 1.2 GB and got the following error message. Error Source: Microsoft Data Transformation Services Flat File Rowset Provider Error D...more >>

We have migrated a SQL Server based system to a new server. Along with that, we copied over some very simple DTS packages. They each export data to .txt files. When I click on the packages to view the design, I can click and see properties for the Source and Destination nodes but when I try...more >>

This may be a bug in SQL 2000 - If the c drive runs out of space, a job that calls a DTS package will show success even though the package log shows that the package never even started. It could be that it caused the tempdb to run out of space - not my design to put tempdb on a small c dr...more >>

User switched from a W2Ksp4 computer to a XPsp2. He had a DTS that grabbed a db3 file (created in VFP) and uploaded it to the server. Now, when he tries to run it from his new XP machine, it generates the following error: Step Error Source: Microsoft JET Database Engine Step Error Descript...more >>

Was wondering since the product changed quite a bit, if I should be posting my questions to a different newsgroup. Thanks Roz ...more >>

I have a DTS with some "transfer data task"s. To get a succesfull run of the dts, some properties have to be set dynamically, so i added also a "dynamic properties task". i use beside 1 global variable also an INI file, and that causes a problem. In this INI-file i have 1 section and in thi...more >>

Hi Get this error Not enough storage is available to complete this operation" error message when you run a Data Transformation Services package in SQL Server 2000 Any ideas on how to fix...more >>

Hi All, Sql server 7.0 We have DTS package which runs evey hour to transfer data from access (.mdb) file to sql server databases, just some time back it was working fine.. but now if fails.. below is the error message from view job history .... DTSRun: Executing... DTSRun OnStart: Cop...more >>

Hi, In a DTS package, I have a SQL Task - see below... EXEC xp_cmdshell ? I'm trying to pass a parameter from a global variable into my SQL statement, but it won't let me add a parameter and returns syntax error. I have also tried 'EXEC xp_cmdshell ?,?' as there are two unnamed parameters...more >>

Suppose I have two identical databases, the first is the staging that all development stuff gets reviewed before the roll up and the second one that is the production. Is it possible in SSIS to have an OLE DB Source Data Flow item that is parameterized, so if I change the value of the parameter,...more >>

Hi there, I have a dts package that first pulls records into a temp table. I also have a Global Variable of type boolean called ContinueProcessing. Once the task runs that populates the temp table I have an Execute Sql Server Task that checks to see if there are records and if there are it s...more >>

Greetings, I'm new to DTS and have a question. I'm creating a DTS package that has ActiveX script to create/open an excel application on a share. However, when I excecute it gives me a package error: Error Description: Error Code:0 Error Source=Microsoft VBScript runtime error Error Descri...more >>

I have this following activex script in my DTS package. When i run it, its saying: " The Transformation is executed with a temporary file as destination. The destination you selected in the transformation is not affected." The code I have is [code] '******************************************...more >>

Hello, all. Just started using SQL 2005 this week, and am migrating some SQL 2000 DTS packages to our new server. These packages are simple flat file comma delimited source file to SQL table destination packages. Surprisingly, and sadly; I've noticed once migrated to SQL 2005, my column map...more >>

What SSIS/ETL books would you recommend? ...more >>

Does anyone have any sample ETL advice? We need to create a SQL Server 2005 lab in order to compare and contrast ETL on SQL 2005 vs. SQL 2000. ...more >>

I can not bulk insert a network file onto a remote SQL server 2005 machine I get this error when run from Query analyser in remote machine Cannot bulk load because the file <File Name> could not be opened. Operating system error code 5(Access is denied.) It used to work in SQL 2000 We just ...more >>

It seems so simple; within a single Execute SQL Task, insert a new row into a log table and store the new Identity column value in a global variable. Yet I can't get it to work. I started with this: DTS pkg global variable: intLoadID (int) which I set to zero. Execute SQL Task: INS...more >>

I have a problem when I try to execute some DTS packages from SQL Agent jobs. The DTS Packages collects data from a Paradox (Borland) database located locally on the SQL Server machine and inserts the data in a SQL Server 2000 database. These packages works perfectly when I Execute them manual...more >>

Hi. We have two SQL Servers. Currently, we have been successfully transfering flat files to the 1st SQL Server, loading the DTS Package via VB.Net, changing the location of the source file and executing the package. This seems to work a treat. We now need to introduce a second server. A...more >>