I need to setup a process that would pull data from different source, compare it to "local" SQL Server 2005 database resolve and update data differences. So, I started new SSIS project, hoping I can figure out things as I go, that's how I learned DTS after all. But I am totally lost. How bad...more >>

SQL Server 2005 Integration Services with Visual Studio 'Integration Services' project. I need the ability to run a package on "Working/Banking" days. I created a store procedure to look in a table to determine if the date passed (Today) is a working day or not. It returns Y or N. I marke...more >>

Hi, The tables are only partially loaded before the failure. I'm using DTS to transfer 9 files from 1 ODBC source into 9 Sql Server table. However, to try and avoid these exceptions, I have only allowed 1 task to be executed at a time (within the Package Properties). I am not using any...more >>

If I use DTS to import a text file into a table that has an identity column and then queried the table and ordered by the identity column, am I guaranteed to get the rows back in the same order they appeared in my import file. I have tested this with a small file and it did, but not sure if...more >>

Hi, I've written an ADO.NET 2.0 provider for our company's database and a Visual Studio DDEX design time support proivder. Everything works well and the documentation supplied on MSDN and samples were excellent. However I can not figure out how to get the SQL Server Integration Services to...more >>

I have a Partitoned view that UNIONs 65 underlying tables with the same schema. This was originally implemented in SQL 2000. I upgraded to SQL 2005 Standard Edition and am now trying to write an SSIS package to load data from text files into these tables. Since each file would load into on...more >>

I am receiving the following error message in restoring and saving DTS packages in SQL Server (SS 2000): "Class not registered" First time I have encountered this issue and will much appreciate suggestion on how to fix. ...more >>

Can the transformations be changed "dynamically" during runtime? I plan to set up a single package with two SQL server connections (source and destination) with global variables (using dynamic properties task) for: 1) source server 2) target server 3) stored procedures (runs on the source ser...more >>

I am encountering a weird issue with SSIS. I have a very simple SSIS package that executes a .bat file. Here's the actual file it executes: @Echo Off c: F: cd ReportingServicesScripts rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest EXIT The .bat file executes a R...more >>

Hello, We designed a DTS package in our SQL server 2000 environment. It executes fine, no problems so far. When we schedule the package it works fine on the scheduled time. But when we open the schedule tab at the specified package to view the schedule it seem it resets to a daily cycle at 0...more >>

Hello I'm using SQL2000 DTS services to export stored procedure result to EXCEL file. When I open EXCEL file, all the data is prefixed with ' character like 'AAMURUSKO 'PIHKALANKATU How to avoid that ? Kuido -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums...more >>

The DTS package reaches out to another server on the network to pull in a text file. The DTS package runs fine when launched from Enterprise Manager or if scheduled as a job. It FAILS to import the file when launched from an ASP page. The other tasks in the job run fine (backup and delete f...more >>

I have a column containing string data like the following: CORP/EAST/TENNESSEE And I need to transform this data to split out the names using the "/" as the delimeter. I was doing this in Excel using "Text to Columns". Please let me know how I can accomplish this in SSIS. Thanks -- ...more >>

Hello I want to convert a datetime to a julian day. TSQL has a DAYOFYEAR function but SSIS Expression builder does not. Is there a way to calculate the day of the year in SSIS expression builder. For example Today, March 28th, 2007 is the 87th day of the year. I am looking for a functi...more >>

I'd like to be able to execute an SQL command automatically when the database is brought online (or attached or the server starts). I assume it'll require DTS, but I don't even know what to ask for in Help. (SQL Server 2005 SP2)...more >>

Hello, everyone! I'm attempting to develope an SSIS package that uses variables passed to determine selected values. I'm using an OLE DB Source, and passing the query in that source values from paramters. For example: Select * from dbo.Test where Value1 = ? This works fine. Howev...more >>

Hi, I need to Import an XML file in to SQL Database using SSIS, Plz let me know how do I acheive this. I am quite new to SSIS, So if you can Point me to some samples on importing XML files using SSIS, nothing like that. Any additional references, if any, are most welcome. Thanks in Advan...more >>

Hi, I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy Database option from Management menu. I want to transfer all tables, and all stored procedures. I've tried everything for the past 3 days, and it didn't work. I always get the same error. Below is the error that I...more >>

This is a long explanation, so apologies in advance!! In a Control Flow I can use precendence constraints to determine how and when things run. However, I've got a need to do something similar in a Data Flow. I have a dataset that I need to perform some work on, as follows: 1. Sample ...more >>

We have just upgraded our 3 main production servers to Windows Server 2003 from Windows Server 2000, these consist of a file server, SQL server and exchange server. We are having problems running SQL jobs that run DTS packages. The error message is as follows :- Error string: DTSStep_DTSDa...more >>

Hey, I just installed sql server 2005 onto a new machine to play around with it, having used client tools from sql server 2000 on an old machine. I connect to a remote sql server 2000 database and pretty much all the functionality is there using management studio - however I am having issues wit...more >>

I have a pump that does one data pump and then runs a series of sprocs (In Execute SQL tasks) on the data to transform. The transaction file is growing quite large (I don't have access unfortunately to the customer's production server). If the DTC service is not started, that means I cannot use ...more >>

Hi, I can't Copy a database from Sql Server 2000 to 2005. I am using the Copy Database option from Management menu. I want to transfer all tables, and all stored procedures. I've tried everything for the past 3 days, and it didn't work. I always get the same error. Below is the error that I...more >>

Hello all, Microsoft provides a report pack of RS reports to use with Integration Services standard logging. The link is here http://www.microsoft.com/downloads/details.aspx?FamilyID=526e1fce-7ad... I have been trying to use these reports in the UK and have come across a problem wi...more >>

Greetings, Well I don't think I have an Orphaned User problem but similar. I DTS copied a database to a new SQL Server. It seems that all my user ids have lost public role rights. Why I think this? sp_change_users_login 'report' returns no entries. So I assume that means that there a...more >>

Hello there I have Sql server 2000 SP4. I've just created 2 DTS's that create text files form views on my databases. When i run it from dts managment it works fine But when i scheduale the dts. the job run failed: Access denied what i need to do in order to run it propery? ...more >>

Hi, I have a SSIS package with sql command for SQL server 2005 and VS2005. First, I create a database in the database Master using the commande USE [master] then I had tables in that new database using the commande USE [new database] and the command USE does'nt work any more. All the tables ar...more >>

I have the following in a dts exec task: exec master..xp_cmdshell ftp -i -s:d:\laufer.txt laufer.com I get an error Line 2 Incorrect syntax near "-" This runs OK in a dos batch file. What do I need to change? Thanks. ...more >>

Hi, I need to import via DTS a text file with 3 fields into a table with a counter primary key and the same 3 fields. When I execute the DTS, I've got an error saying null values are not allowed for my primary key. But in the Transformations via DirectCopyXform, i haven't selected this the ...more >>

I would like to be able to store connection information (like server name and db name) for Analysis Services as well as SQL server connections for SSIS tasks in an Sql server table and to retrieve them at run time. Any advice?...more >>

Hello All Quick question I have a package that I am developing that is going to have a bunch of connections in connection manager. Lets say the number is 20 just so everyone doesn't go crazy. They are all File Connetions pointing to specific files in a folder. I am creating the package on ...more >>

Has anyone come up with a solution for copying a dynamic table list? I have a source with 50 or so tables my destination has the same schema. Is there a way to create a loop routine to copy the source data to the destination? I really don’t want to create 50 copy routines and then have to m...more >>

Hi all, I want to connect from a Client workstation to the SSIS engine of my SQL2005 server. But, the only choice of authentication I have is Windows Authentication. Is it possible to change it and use a sql account to log into the SSIS engine? thanks in advance. Regards Cédrick B....more >>

Hi, I am really new to SQL SERVER 2005, i am not even familiar with Creating procedure So what i am trying to do is dump some data form CSV file to a table the following shows the flow of the data base CSV.file --------- > Dump_table ----------> Actual table SSIS ...more >>

I'm trying to execute dtutil from xp_cmdshell within a SQL Script and then populate a log table with the results. The frustration I'm having is I only want to insert the actual result such as success or the error message it creates. I can't get around the rows that have the Microsoft info. An...more >>

I think I have a problem with the transmission speed of data when doing a DTS between two SQL 2000 SP3a server hosts running Win2K SP4. They have the same updated MDAC versions and I've conducted several tests, including running the SQL Best Practices tool to check server configuration parame...more >>

Server.MapPath in SSIS script task? I'd like to read a file (on the database server where ssis is running) into a variable inside my ssis script task. I have a reference to system.IO but get name 'Server' is not declared during compile, so I removed it. Dim FILENAME as String = Server...more >>

I made a batch file which runs an ftp script. I'd like to put this either into a sp. Not sure how to go about this. Here's the batch file: ftp -i -s:c:\laufer.txt laufer.com > c:\laufer.log It uses this laufer.txt The laufer.txt is: <User Name> <Password> ascii lcd c:\temp cd ...more >>

I have been looking to see if anyone has written a script to create the maintenance plans in SQL 2005. The previous commands like sp_add_maintenance_plan have all been depreciated. It appears Microsoft don't want you to script the creation. By having it scripted makes it easier to ensure the...more >>

Hi All, I apologize for the incorrect post. Anymore, Google performs miserably on Searching (btw, does anyone know what has changed in the algorithm for search results?). When I search for "script transformation editor" (with the quotes), I receive 1 hit - this group. What is the proper gro...more >>

I am attemting to move records from a text file into an existing table in SQL Server 2000. I only want to append any new records to the table, ignoring any records in the file that already exist in the table. But when I try to do this using DTS, it sees the duplicate key and just errors out. ...more >>

The below vbscript enumerates password age on computer accounts in Active Directory which is then output to an xml file. Then entry point on the ActiveX Task is Main(). This script runs fine outside of SSIS, but inside of SSIS as an ActiveX Script Task is generates the error: Package Valida...more >>

I am using the following VB.NET code to pull queries programmatically from an SMS Server and dump the output the an XML file. This data is later used in multiple ways, one of which is dumping to a SQL database. This runs just fine if run by itself, but I would like to be able to run all of t...more >>

Everyday I have to go to a website that displays a page of formatted text information, I then save the page using Save As...txt to a directory on our network. Is there a way I can create a SSIS/DTS package to open that page, save it as a text file and store it in a set network location? Tha...more >>

For the last three years we have been running DTS jobs by executing a stored proceder. We now want to upgrade our SQL Servers to Windows 2003 server on a more powerfull server. The DTS jobs will run manually but we can't get them to run using the stored procedure method. The error return...more >>

running DTSRun /S "servername" /N "package name" G/ "packageguid" /E DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecutePackageTask_3 DTSRun OnError: DTSStep_DTSExecutePackageTask_3, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Conne...more >>

Hello, I am trying to insert a row count into a log table after a bulk insert is performed. I have put in the Row Count transformation task, storing the value in a user variable, RowCount. At the end of the bulk insert, I am executing T-SQL, creating an index, other stuff, and then I want to...more >>

Hi, I'm a Newbie to DTS who needs to import data from an excel spreadsheet into SQL2000. When I use the import Wizard, the tables to be imported (sources) include all the pages from the excel spreadsheet as expected, however an extra source for each page is shown with the same name plus a $...more >>

I need to create a text file with data from a table. No problem, I've done that before. Then I discovered I need to select rows based upon a parameter. Still no problem, I know how to pass parameters to a DTS package with DTSRUN. I created a package with: 1) A global variable to contain the ...more >>

Good afternoon, all, I have an Excel workbook that needs to be imported. It has three sheets, but it's really the first that is giving me fits. Each of the three worksheets have header info and instructions on the first 8 rows. Worksheet 1 then has, on row 9, the column names for the group ...more >>