Hello all, A while back I downloaded the sample Loop-Import-Archive package from www.SQLDTS.com and have put it to good use. The problem I'm running into now is that instead of importing from a flat text file, I'm trying to modify it to import form MS Excel. I need to import 2 different she...more >>

We are trying to embed RunDTS.exe into a batch file to kick off a DTS package. The batch file is run by a user from their workstation. When logged in as Administrator, it runs beautifully. When run by the user, the error listed at the end of this post occurs. The user cannot be allowed ...more >>

We've been using DTS for several years as part of a commercial application, and it's worked fine, and still does in SQL 2005 using the backward compatibility components. We've got existing code that uses the DTS API, and I'm not sure we need to move to SSIS if DTS is working for us, considerin...more >>

I have a flat file that has both header and detail records in it. The file is a fixed length and an abbreviated form of it would look like this (the real file has lines that are 450 chars long with 50+ columns depending on if it's a header record or detail record): 12340001SomeDealer SomeDe...more >>

Hi all, This question is regarding downloading a file from an FTP Server to local (through SSIS FTP Task). I have 2 questions to ask you. 1. I have Downloaded a file of 5.5 MB from FTP Server to Local through (SSIS FTP Task).It took nearly 4 Minutes 20 seconds (N...more >>

I need to submit multiple Teradata statements using same open connection; The first statement is a stored procedure that inserts data into a temp table in teradata, the second retreives with a select from that table. If the connection from first is closed then the data in temp table does not ...more >>

We need to have a DTS package that takes in a parameter and exports records from a main table that match the parameter and then exports records from other, related, tables. Then a DTS package that imports this same data into another db of the same structure. Can someone help me get started on ...more >>

Hi, I need to find out how to process an AS 2000 cube via vbscript. So far I have set the following properties: ' *** Set Task Properties oOLAPTaskProperties("Description").Value = "Process MyCube" oOLAPTaskProperties("Datasource").Value = "MyDatasource" oOLAPTaskProperties("TreeKey"...more >>

Hello Everyone, Will upgrading from SQL 2000 Standard Server to SQL 2005 Standard Server break my exisiting dts packages? We have a large number of packages and I am hoping that I will not have to recreate them. TIA, Chris...more >>

Microsoft says it is possible but I just do not see how. Here is the link to the help file where it said that variables could be pass as input to web methods...I do not see the check box they mention on my IDE. Any help would be greatly appreciated. Thanks, Dani ...more >>

With SS eval (enterprise edition) I cannot see the template for SSIS projects (only the one for Reporting Services) - can anyone help? The report for setup says the SSIS service is correctly installed and the service is running......more >>

I have the DTS package as follows: Function Main() dim i_Day dim i_Month dim i_Year i_Day = Mid( DTSSource("Col002") ,7 , 2 ) i_Month = Mid( DTSSource("Col002") ,5 , 2 ) i_Year = Left(DTSSource("Col002"),4) DTSDestination("Col001") = DTSSource("Col001") if IsDate(i_Year & "-" &...more >>

Hello , I wanted to know how to make a dynamic FTP Connection. My FTP Server will be having a FTP server Name , File path, FTP Server Name, FTP User Name, FTP Password. Our Database maintains different FTP servers for different clients.In DTS we used below code in activex script ta...more >>

Hello, i'm using ssis on a machine with 2,8 Ghz, 4 GB RAM and more than 10GB storage capacity left on all disks. When I do a single click, for example opening a dataflow task or even click on it's border to change the label, VS 2005 reacts with a delay of two or three seconds. The whole G...more >>

Hi, i'm a bit confused about how protection levels, deployment and configuration fit to each other. I have sensitive data in my connection strings (such as passwords) and it's out of my reach to change this design decision. These info should be kept independent of the user accessing it,...more >>

I have created a SQL Server 2000 DTS package that exports data to an excel workbook. It runs successfully every time that I execute it from the DTS portion of Enterprise Manager. I created a job by selecting Schedule Package and when I attempt to run the job it fails on the first step w...more >>

Is is possible to sum data being imported in DTS without actually putting the data details on the 'hard drive'. I want to SUM fields on some incoming .TXT records. I don't actually want all the indiidual recods just the sums....more >>

I'm attempting to connect to the SSIS service on my server from my local workstation using Management Studio and when I try it I get: ****************************************** TITLE: Connect to Server ------------------------------ Cannot connect to xyz. ------------------------------ ADD...more >>

I need an example or some ideas - how to do this - I need to run a job that searches a directory every hour to see if any files are available for upload - and then upload it into a SQL data base - and then move the file to another directory. I know how to manaully upload the file using DTS...more >>

I'm rewriting all of our sql2000 dts packages in ssis for our upgrade. One of my packages has several ActiveX scripts. The script checks to see if a marker file is available and if it is, it proceeds to run the rest of the package. If there's a better (easier) way of doing this I am all for it...more >>

Hello, I created a DTS to copy some data from a sql server to another. I would like to use a global variable on a Transform Data Task with a sql query like " SELECT * FROM Table Where Id=? " in order to call the DTS with DtsRun.exe and put the value of the global variable on the command line....more >>

I'm new to SSIS and I'm trying to rebuild a sql 2000 dts package that imports a text file. This file has a field that is a string that is a date timestamp. The field looks like this: 20050622180517 in this format yyyyMMddHHmmss. In my sql 2000 dts, i use a date time string transformation to ...more >>

SQL2K SP4 Is there a way that an end user can exec a DTS Package from an application? I don't want to allow them xp_cmdshell or anything like it, so Im at a bit of a loss. TIA, ChrisR...more >>

Our application is used on both SQL 2005 and SQL 2000. Is there a way to copy a database from 2005 to 2000? I would like to start working with SQL 2005 and support SQL 2000 as well. -- Craig H. Anderson...more >>

I have a DTS routine that I have been using to append dates into a SQL Server 2000 table and it usually works great. It appends to a Smalldatetime column -- It is as follows: '********************************************************************** ' Visual Basic Transformation Script ' Cop...more >>

We have recently decided to migrate from SQL Server 200 to 2005. We have many processes built in 2000 DTS packages that pull data in from DB2 via an ODBC connection. Of course, the Migration tool is of no use in moving these packages to SSIS. How to I pull in my DW (DB2) data into SQL Serve...more >>

I need to: 1. Retrieve a subset of data based on data from each source record. 2. Perform some analysis on this data. 3. Set a value in the destination based on this analysis. I'm fairly new to DTS, but after a little research I thought I was on to something with the Lookup feature. After st...more >>

Is there any way I can zip up files within an ActiveX script? Thanks, Ninel -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200608/1 ...more >>

In our application, we would like to export the user data. In the main table, it is related to many other tables with foreign keys. We have not used DTS before and would like to see if it can export the data for us. How can we write a DTS package that will export from the main table, and all r...more >>

I thought picking the correct Provider would have been the easiest task! I need to connect to both SQL Server 2000 and 2005. I can't seem to make the correct selection among .NET, .NET for OLEDB or Native OLEDB. I thought that the correct choices would have been ADO.NET or Native SQL but now w...more >>

Hi, Sorry if I'm in the wrong group for this sql language question, but perhaps someone here can answer -- or direct me to a more appropriate newsgroup? I have a sql query that attempts to select a "DealerClientType" based on a Case statement involving values from other tables. Running the ...more >>

Hi, I am performing database conversion for a database of a 100 tables, each done by separate data transformation task. Task are ordered using precedence constraints. Is there any convenient way of diving tasks into phases (or groups of tasks) and choose on each exectution which phases to perfo...more >>

Hi there, I've planed the excecution of packages, some at different times, some others at the same time. Do you know how I can follow in real-time the execution of the packages with the start time, the current status....etc... Is there a native Microsoft user-friendly tool existing for ...more >>

SSIS 2005: Is there any other Best Practice than using a Lookup Task do an update via OLE DB for known IDs and doing an insert for unknown IDs afterwards to Update a table from an imported table? If doing so the updates are extremely slow. We used a staging table and a "update from where exi...more >>

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 >>