I am trying to connect to Integration Services from my workstation via SQL Server Management Studio. The SSIS service is installed and running on the server. However, when I try to connect, I get the error: "Connect to SSIS service on machine "SQLPROD01" failed: Access is denied." Not sur...more >>

Hello all, I have a list of text data files (*.TXT) that I'm trying to import into my DB. The files have the year and month the in the file name (for example: ABC0301.TXT would be for the month of Jan-03). One of the first steps in my package is an ActiveX Script Task that I want to ex...more >>

I'm trying to create a package based on an ODBC connection, to a legacy accounting database. I've created a system DSN, which tests successfully. In the package designer, I added a connection based on the DSN, and again it tests fine. However, when I add a datareader, and use the connection...more >>

Hello, I have a DTS package that is being run every day. It's been in existence for a couple of years, now, transferring information between two systems. Yesterday, one of our users noticed a small problem and I went into the package to make a minor change. (Specifically, I modified a p...more >>

We have been using the 'Copy SQL Server Objects' with success for some time to copy an entire database to another server. Recent changes to our database infrastructure cause DTS to fail: 1) We have a check constraint which uses a user function (which refers to the table on the check constrain...more >>

I'm struggline with the SSIS Execute SQL Task and wanted to see if it's me or if it's SSIS 2005. I'm finding that I'm unable to add ReturnValue parameters to the Execute SQL Task parameter list after already added the 0'th parameter,e.g. initial syntax is EXEC stored_proc ?, ? OUTPUT, ? O...more >>

I have a table structure that is being used in a number of packages in a create statment. I just noticed that one of the fields should be a numeric type in stead of a char, is there a way to run a search and replace on the Create statement either within a DTS package or through a sp or other ...more >>

I can use LoadFromSQLServer successfully unless the target package contains OLAP tasks, then it appears to hang this procedure. Anyone got a workaround ? TIA...more >>

I have need to produce a report (excel sheet actually) from SQL that would run each Tuesday and each Friday of every week. What needs to be on the Tuesday report is everything that came in from the Friday midnight time, until the Monday midnight time. The friday report(sheet) would have ev...more >>

Hi everybody, I have 1 to n Excel spreadsheets I need to load to SQL tables. Excel files names are table names in database and Excel column names are table columns. I need to check if table exists in db, rename existing one to name_bak, create a new table and insert data into it from Excel. ...more >>

When i try to import a file with 471000 rows of data using Microsoft text driver to a SQL Server (version 7), DTS only imports about 416000 rows and give me a success message. I tried deleting a few rows of data and inserting a few rows of data, and also checked the full data set to make su...more >>

I am not sure if this is possible or not but I am trying to do the following. I need to export data to a test file in the following format: [Users] firstname=john lastname=smith when Creating my table which stores this information, I am using T-SQL to concatenate my values into one row, ...more >>

In part of a DTS package a user will input a value. I need to take that value and update a table. Something along the lines of "UPDATE dbo.OLSParms" & _ " SET dbo.OLSParms.Val = '" & DTSGlobalVariables("Category").Value & "'" From within the activex script task, how would I write this?...more >>

Hello, I need to import data from a flat file into a sql server 2000 database. The Fate file has no delimiters. I have the specs for this file so I do knowthe layout of this file. Can somebody point me in the rigvht direction to accomplish this. Thanks...more >>

I' m using setup program for making my project's install.exe. How can I create my DTS packages from command or query if is possible....more >>

I need to dump Access MDB files to SQL Server 2005. No extract and transfer, just transfer the raw data. However, the mdb files can be reaching the limitation of 2G. Is there any benifit (especially performance) to use SSIS to load the MDB files? Or a simple SQL statement (with openquery to...more >>

I have created one DTS package with 5 tables transfer from source to destination db.I am transfering incremental data from source comparing timestamp column value from each of these tables.for this to i am storing datetime value in global varaible called Last_Export_Date. I am updating Last_Expor...more >>

Hello there I have database with many relationsips At the start it is empty and when i'm trying to import to it data. some of the tables are failed due to key violations Is there a way to import the data in this cases? ...more >>

Hello there I've run dts between two servers that connected by the internet, and one of the tables failed. I've transfered 78Mb, and the log file has increased in 100Mb. Whayd did the log file has increased that mutch? and how can i prevent it? ...more >>

Windows 2000 Server SP4, Dual 2.4 GHz P4 Xeon machine, 3776 MB RAM SQL Server 2000, all patches and service packs applied I am trying to import 6 text files (comma separated data) with roughly 9000 rows and 900 columns (file extension is .txt not .csv). Each file is about 10 MB in size. I...more >>

I have a DTS package with 12 Transform Data Tasks in it (along with 2 data sources and 3 sql tasks). When I run the entire package one or more of the Transform Data Tasks do not execute. I can run them manually, but that is not the point. No error messages are generated. Is there a limi...more >>

I'm using DTS in SQL Server 2000. This is the first time I've used it extensively. I have a task which is trying to "manually" roll back a set of steps if any errors have occurred in a previous step. In the Execution Progress dialog, the previous step shows up with "Errors occurred (nnn)". Yet...more >>

If DTS is not supported in the new Workgroup version of SQL 2000, how does one get data and SPROCs in and out of a server? Is there some replication-like process to transfer from one SQL site to another without all of the transfomation capabilities of DTS?...more >>

Hi, I recently upgraded my dev machine to SQL 2005. It's the only machine on our network that's running SQL 2005; everything else is on SQL 2000. It's been a bit of a struggle getting used to the new management console, but mostly liveable. However, now I'm getting totally frustrated by ...more >>

I have a DTS package that performs data transformation from a flat file to a database table. I've added two columns to the table but, the two table do not show up when I look at the transformation tasks in mu DTS package. Shouldn't the new columns show in the transformation step automatica...more >>

Hi, Is it possible to output the content of a table into .txt file using UTF-8 encoding and how? Thanks in advance. ...more >>

Dear all, Since I'm reasonably new in this field, I hope you can help me with a strange problem. At least: for me it's strange! I have made a few data copy-and-filter steps, copying data from one table to another, using DTS-packages based on queries. These DTS-packages are combined in a ...more >>

In SQL2000 DTS you could execute a package from the DTS designer and see how long each step took. Is there any way to do this in SQL2005 SSIS? -- Jeff Lynch "Ramblings From A [Microsoft] Connected Universe" http://codebetter.com/blogs/jeff.lynch ...more >>

Hello: I have a web application from where I need to execute a DTS, my platform is SQL 2000 and .net 2003. From a Vb.net module I want to execute the package, my question is: does the package need to be STRUCTURED STORAGE FILE or could it be SQL SERVER package (local to the server)...more >>

I have a flat file with 3 million records.One of the field is numeric. What I am trying to do is transform the flat file data into sql table.The DTS Package fails. The Reason it fails is that one of the Numeric Field has Blank value in it.what do I need to do within a DTS Package so it convert...more >>

Hi, We have a large development team and they create DTS to load some data into DEV environment and often need to change the connection properties. Since only members of sysadmin role has access to change the settings if there a way to give access to the developers so that they can modify the...more >>

We have a number of DTS packages running on a SQL 2000 server, pulling data from a non-relational accounting database. The package is set up using an ODBC connection to this database, which uses a system dsn on the machine. I want to re-create all this over on a SQL 2005 system. I have ins...more >>

I need to move some data generated from a SQL Server 2000 scheduled job over to Exchange 2003 calendar. After some research, it appears that a DTS package could do the trick for me. My questions is, does anyone know how to connect to Exchange from SQL DTS? I know there should be some suppor...more >>

I am trying to copy a "snapshot" of a remote sql server 2000 database to a local sql server 2000 database. I just want to run a simple Copy Objects dts, but it is choking on a user defined function. Another object needs the udf but the udf hasn't been created. Is there any straightforward...more >>

Environment: SQL 2005 EE RTM I am trying to provide a dynamic XMLA string to an Analysis Services processing task using a package-level variable. My XMLA string is about 5400 characters long. I am able to put the value into the variable in a Script task, but it goes down in the AS processing...more >>

Hi, Environment : SQL Server Intergration Services Beta 2. Requirement is that moving the contents of all CSV files in a particular folder to multiple tables in database. What we have done so far. In the Control Flow task we have used the Data Flow Task in Foreach Loop Container in order...more >>

Hi there, In my DTS package, I want to define my connection DataSource using dynamic properties. I'm using a query to initialize the source. Can I use a global variable in this query and how to use it ? Thanks for all VinCe...more >>

I'm sure there is a simple answer to this but it eludes me at the moment. I’m using the Developer Edition of SQL Server 2005. The MSDB node under Integration Services->Stored Packages refuses to open. It always fails with a login timeout message and that the connection was actively refu...more >>

How can I insert the results of a query into another table? TIA RC- ...more >>

Facts: I have a Windows 2003 Standard server that has SQL 2000 Standard installed. Both MSSQL and ServerAgent services are running under a windows user. Issue: I created a simple DTS package, and I want to be able to run it in a scheduled job. I have created the scheduled job for the package...more >>

I am trying to import data into SQL 2000 through an ODBC and keep getting an error, I tracked down the error and it is because the program that I am trying to pull data from has invalid dates (by design, not mine). It uses the 31st day in all months. I am trying to get figure out how to correc...more >>

I have a classic one. Never the less I cannot get around. When I run package, it executes fine loading file from specified location. Schedule job fails when tries to run that package reporting ‘Error string: Error opening datafile: Access is denied’ Facts: Path indicating file does ...more >>

In my ADP I start a DTS pakcage from my vba module (use the object dts.package2), I display the status of the progress in a pop up form. My issue is the DTS package takes ages to complete and if a user use another application and come back to the Access application the got a pitch white screen l...more >>

Hi, i have created a stored procedure and its running fine. i am sure that sproc doesn't have any problem. after that i integrate this sproc to an application. whenever i execute sproc from query analyzer. its running fine and doing expected job. but whenever i am calling it through applicat...more >>

Hi All, I am trying to import all tables from an Access 2000 database into a SQL Server 7.0 database. The import seems to work fine and says it was successfull, but the tables are not listed under the Tables node. If you look at the permissions for the Public database role, the tables are lis...more >>

Hello, I have a text file that is 27 megs. I import it into a single SQL server varchar field and delete some rows that aren't properly delimited. I then output the file to a new text file. The text file comes out 405 megs! I have tried shrinking the size of the varchar field and change nvar...more >>

I have the developer edition of SQL Server 2000. I also have a DTS package that imports from a text file into a SQL Server 2000 database table using the standard ODBC driver for SQL Server supplied with DTS. It worked fine UNTIL I added ONE column to my destination list in order to import furt...more >>

I have two tables that I need to compare. The purpose of the comparison is to check for duplicate records, or better said, check for new records in the newer table. I then want to add the new records to the old table. The newer table is expendable but the older table is the actual productio...more >>

Hi, Im having trouble de-activating a task (DTSStep_DTSDataPumpTask_1) if a certain datasource has no data in it. The error which i am getting is: Invalid Step Result Value. Could anyway help? Raj This is the script i am executing: Function Main() Dim oFSO, File, oStep, oPkg,...more >>

Dear All I have arround 100 packages in all packages I have put IP of the server in the connection. Now I want to use all this package on other 2 servers. So I need to change the IP in the connection for all this 100 packages. Is there any easy way to do this? I dont want to open each ...more >>