I have a flat file with row delimiter <cr> & <lf> and column delimiter <tab>. This file has certain rows less columns than other rows. The rows which have less columns have early termination with <cr> & <lf>. When I parsed this file, I found SQL is not inserting missing column delmiters and sh...more >>

Hello- I want to use the Execute SQL task to run a stored procedure, then save an output parameter of that stored proc into a global variable. Here's how I set it up. I created a global variable called gGroupID. I added an Execute SQL Task and used the following as the SQL statement: DE...more >>

I am trying to produce a result that includes only records with a Status of "A". The DTS Package is as follows: Function Main() If Instr(DTSSource("Col001"),"A") > 0 Then DTSDestination("ACTIVITY-STAT") = DTSSource("Col001") end if Main = DTSTransformStat_OK End Func...more >>

Hello, Every night, I would like a job to backup our production database and restore it to another database. The second database is to be used for testing, but we want to keep the data the most current. Does anyone know if this is possible to do? We tried doing an export, but it takes wa...more >>

I am very new to SQL server and am looking to use the DTS to exports some data to flat files. Is there an easy way to add header and footer records to the file? -- Jon Larkin...more >>

Just getting the hang of this SSIS stuff. I have an operational Control Flow and Data Flow (Input from Source, Data Conversion, Output to Destination). All works fine. However, I would like to limit the Output to specific rows based on criteria (like a SELECT and send the result set to the...more >>

Hello, When I designed and deployed DTS packages, I often stored them in SQL Server or in the file system and ran them from batch scripts (using DTSRun.exe) or from ASP.NET applications (either case, most of them weren't run at the SQL Server itself). I was hoping I could do the same wit...more >>

I have a single SSIS package that executes 10 different SSIS packages. All are stored in the sql server package store. When I execute the outer package manually, it completes successfully. If I execute it from a sql agent job, it fails. Also, if i change the job to point to each individu...more >>

I have an execute SQL TASK going and after it completes I want to go to a Microsoft OLE DBServer. It wont let me use the regular after completion bar, any ideas?...more >>

So, I used the SQL Server (2005) Import and Export Wizard to create an Import operation. I saved it to SQL Server instead of the file system. Now I can't find it!! The help files states: "If the wizard is started from SQL Server Management Studio or the command prompt, the package can run ...more >>

I have the following command that imports my package into SSIS but i can't seem to set ProtectionLevel 5 (ServerStorage). I can do this manually if I select import package but not from the command line. DTUtil /DestS SERVER\INSTANCE /FILE "D:\MyPackage.dtsx" /COPY SQL;"\Packages\MyPackage" ...more >>

Hi everyone, Short introduction; I work for Biretco BV in the netherlands; we are a retail organisation for bicycle stores! Now the case is : Everyday we sell products in each store. (approx 500 shops) At night the shops upload their file on our FTP.. I collect the files (there are 500shop...more >>

Hi, Do you know how to run a lookup query with multiple parameters? I am passing them, using a comma: DTSLookups("AddPart").Execute(Value1, Value2) But every time get an error "cannot use parentheses when calling a sub" As suggested in some groups, i tried Call DTSLookups("Addpart").Execute(Va...more >>

I would like to execute a ssrs report from within a ssis foreach loop but there is no task especially for this so I tried to use the web service task and connect to the ssrs web service but ssis complained about the ssrs wsdl, does anyone know why and what I can do to fix? Thanks. -- John P...more >>

Our product uses a DTS job to copy between two databases. Whenever we modify this DTS package, we notice that the Owner and Machine fields are automatically filled in (when you view the Package->Properties values). The problem here is that usually one of our devs modifies the job on his box an...more >>

Is there a way for DTS to use a directory server as a data source? The only use i've seen for the directory services OLEDB provider, ADsDSOObject, is in the typical workaround suggestion using a query/view thusly: EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSO...more >>

I'm looking for a way to have a DTS job to shut down windows once it is complete. Is it possible, perhaps through an ActiveX script? Thanks! ...more >>

I'm building the dts from scratch... The goal is to build a dts t export some data (sql statement is a must) from one table to a tex file. I've built a package, connections (two of them, one for the sq server, and one for the text file), steps (only one), one task with on custom task, and one t...more >>

Are there some errors which are not trapped by the Failure workflow on DTS ? I set up a package to load data from one system to another. It failed because the file was in use. The following step of the package was set to execute on the Failure of that step but it did not run. I tried chang...more >>

Hello, I am trying to generate a DTSX via C# Source that just copies one table column into another table. Starting with a already existing package that does that my analysis failed finding all information within the package like sourcetable or destination table. Where I can retrieve all necess...more >>

Hello, In my SSIS package, I generelly load a lot of data for all customers from database A to database B. However, sometimes, I only want to load data for specific customers. I've added a string variable used as a parameter called CustomerFilter, and a SplitByComma SP which, given a comma-...more >>

Hello All, Wondering if anyone ahs thoughts on what the best practice for ownership of a DTS package is? We have several biz-critical packages that are owned by individual developers. One of the owner/developers has recently terminated employment and we wish to remove the associated login. ...more >>

can anybody tell me the best way to load data fast using SSIS? I'm trying to just disable the indexes on the destination table before i truncate and then load, but using the fast load option gives me an error saying that it was unable to produce a plan because the clustered index is disabled....more >>

I need to create a DTS package that checks for files on a server and if they exist I need to import them into a SQL 2000 table. I created an ActiveX script to check for the files, on success of the scripts I run a data transformation from the DBF into SQL. The issue is if the first file do...more >>

Hi Everybody, 1) How Do I execute a DTS package from ASP? 2) How Do I execute a DTS package from a Stored procedure? Any help is appreciated. Thanks, Sunny. ...more >>

Hi, I used to use DTS for transferring data between Informix and SQL Server and it worked very nicely. I now need to replicate data between Oracle and MySQL and need a similar tool. I can't use DTS because I no longer have SQL Server, and I can't use Oracle or MySQL's replication service, bec...more >>

Hi, I'd like to import rows from an Excel File to an SQL Server 2005 table. One column of my table is 500 varchar big and my Excel column contains 259 letters. The import task aborts because he has to truncate this particular column. When loading all failed rows in another excel the specific ...more >>

I have a DTS Job with a Global Parameter set. When I use the ? I get the error 'No value given for one or more parameters'. I have done a search on this newsgroup but only found a response concerning comments. I have no comments in my Query. Is it something to do with the Disconnected...more >>

I used the following SQL query to insert data into another table using DTS connection. I thought that following will insert the rows from a table 10 times into the destination database. However, insertion is done only once. DECLARE @count integer SET @count = 0 WHILE @count < ...more >>

Hi, We are in a process of cleaning all our databases. We want to delete all the objects( Basically tables, Views, stored Procedures) which have not been used since 2003. My question is how to figure out that when was the last time the object was accessed and who was the user who accessed ...more >>

I want to use execute pacakge tasks in my SSIS solution so that when a package is called the relative path of the parent package is used to locate the child package. My first attempt to do this was to remove the hard coded path when setting up the file connection, C:\Package1.dtsx replaced with...more >>

Has anyone written a script or know of a script I could run to tell me what SQL Agents jobs were running at a given point in time? Thanks in advance, Mark ...more >>

How exactly does SSIS security work? I thought the reason why db_dtsltduser, db_dtsoperator have read access to a package is because they are by default assigned the Reader Role on the package. But if I create my own database role in msdb, call it MyUsers, then replace the existing <default: d...more >>

Hi, I’ve created DTS Package in my local machine for development purpose Now I’ve finished the development phase, I want to copy the file to the database server -- How should I do it? I have tried “Save As” – but from the server when I open the package file, I received msg as following: ...more >>

I've made a DTS package in SQL server 2000 to import tables and data from an Access 2000 database. Everything works fine except for one thing: The table relationships aren't there...does anyone know why and how I should do it instead? /Roger ...more >>