Legacy DTS - Is This The Right Approach?? (please See Post)
Mar 6, 2007
Hi,
I am converting several DTS pkgs to SSIS. Several of the pkgs contain complicated "Active X script" transformations on text files. That is, it would take me a long time to have to re-write this!
In the meanwhile, do you think it's just best to use the EXECUTE DTS 2000 task until I have a better grip on SSIS??
Also, what is the equivalent of "Active X script" validation in SSIS?
For example, I have an Active X script that checks the values of a particular column in a text file. If the column contains a datefield, then load into the database, if not, then discard... what task in SSIS would replace this logic? (not now, but for later reference)
Thanks so much
View 1 Replies
ADVERTISEMENT
Oct 22, 2007
---------------------------------------------------------------
My Original Post
I have to query n table(NLRImports) using the Distinct keyword, to retrieve a set of ID numbers. ( "Select DISTINCT id_nbr from NLRImport" ).
Now i want to use those values i retrieved, to process the records in the table(NLRImports) 1 by 1. How do i use those ID no's i retrieved as Variables or parameters for my next query?? If this makes sense?
----------------------------------------------------------------
First, thanks for the response.... now here is what im trying to do.
I created a simple application in delphi to import information to a table in MSSql2005. This is some of the resulting columns...
date | id_nbr | account_nbr | sub_account_nbr | ... etc
-------------------------------------------------------------
Now there will be several entries with the same id no but on different dates, so i take it dates would rather be my pkey.
Then i need to take one person's entries(i work on id_nbr) and go thru all the entries taking the earliest date and comparing all the other entries for that person to the first date and select all the dates more than 19 days after the first date and less than 91 days from first date and place it in a new table.
I used cursor s and while loops to kind of get it going but i know that cursors are not really recommended use but the performance implications dont bother with this particular job.
What other ways should i be using to accomplish this?
thanks, i hope this is clear...
View 1 Replies
View Related
Mar 16, 2007
Hi,
I have imported 3 DTS from SQL 2000 to the SQL 2005 server. The wizard went fine, everything is ok. When I close the wizard window, I cannot see any of them. If I reimport it, it asks confirmation to override it. Where are the 3 DTS in the management Studio if they are not under Legacy DTS?
Thanks
View 4 Replies
View Related
May 30, 2006
I am in the process of migrating to a new SQL 2005 server. I have a number of DTS packages on my SQL2000 server, approximately 200, that are used on a daily basis. I used the migration wizard to migrate the packages from the 2000 server to the new 2005 server however there are issues with the way some were brought over. I would like to have all of the packages moved from the 2000 to the 2005 server and appear under Legacy DTS so that I can run them as 2000 DTS packages unitl I have a chance to correct the issues.
Here is where my question lies. The migration wizard migrates upgrades all of the packages. How do I move them from the one server to the other and perserve their 2000 DTS format? The servers are on 2 separate boxes with different instance names. Everything I've read tells you how to run the legacy packages but nothing seems to explain on to move them.
Any help would be appreciated
Pete
View 5 Replies
View Related
Oct 4, 2006
How can I scheudule a legacy DTS 2000 package if it stored in SQL Server itself?
I assume the package 'lives' in the msdb database.
For what bizarre reason is there no option to schedule legacy packages anyway, why provide the DTS legacy/designer components if the ability to schedule them isn't possible?
Is this microsoft's subtle way of telling me that I should convert them to SSIS packages ...I just don't have the time to do that...help
Thanks
View 5 Replies
View Related
Mar 8, 2007
Hi everyone,
I am trying to migrate DTS packages from sql2000 To SQL2005 server. I am running the migration wizard from Data Transformation services under the ManagementLegacy Node on SQL 2005 server. I get to choose the packages to be migrated, but each of the selected pkg ends with a progress of "STOPPED" in the wizard and the outcome of the wizard shows as Successfull with a chaek mark on Top Left corner.
But No packageg appear under the location reff. above. I like to know if anoyone has a solution for this issue.
Thanks
sekhar
View 1 Replies
View Related
Apr 20, 2007
I have changed a legacy dts 2000 package. It resides on a sql 2005 server. How do I schedule it? That option doesn't look like it is available for legacy 2000 dts packages.
View 4 Replies
View Related
Mar 24, 2006
I have a legacy DTS package on my test SQL Server 2005 in the ManagementLegacyData Transformation Services folder. I can run the package, but how can I schedule it?? this doesn't appear to be an option anymore like it was in 2000.
Thanks
View 4 Replies
View Related
Apr 28, 2008
I am trying to import a legacy dBase III file (.dbf format) into SQL server. The file contains timestamp fields which, as implemented in the dBase data file format, are actually eight-byte character strings. I am using this command:
SELECT * INTO LegacyData FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:Files','SELECT * FROM data.dbf')
The command fails with this error:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
This is happening because some of the datetime fields contain strings that can't be parsed by SQL as valid dates and times. The legacy application which created the data file apparently indicated a missing timestamp by storing "- - " as the character string.
If I change the select statement to say "select top 2 *" to only import the first two records (neither of which happen to have any invalid datetime values), the records are imported successfully. What I would like to do is to import all records and either skip those records that have a bad datetime value or, better yet, import all records converting invalid dates to null values.
I tried changing the select statement to include various types of casts but it seems that because the .dbf file indicates that the data field is of time timestamp, SQL will always try to read it as a datetime field regardless of how the select statement is written. I don't currently have any way of modifying the dBase III file or I would attempt to search for and remove the offending records.
Does anyone know of a workaround for such a situation? Is there a way I can import the data using SQL server or will I need to find a dabasebase conversion utility that can handle unparseable date strings?
Any suggestions would be appreciated. Thanks
View 12 Replies
View Related
Sep 7, 2007
Hi Guys
There are a few dts packages in our sql server 2000 database which we schedule daily for at night for business purposes.
how can i schedule the same DTS packages in Legacy SQL 2000 DTS when we migrate the Databases across to Sql 2005.
Can that be done or we need to migrate the DTS Package to SSIS
Vic
View 1 Replies
View Related
May 22, 2006
My manager wants me to produce a legacy dts-style display of an executing package in an asp.net grid view. It would be color-coded the same way: red, green, black showing the status of each step with start and finish time. Any ideas on how to do this?
View 3 Replies
View Related
Dec 29, 2005
My packages are migrated over our new 2005 server. How do I schedule/run them? BOL seems to suggest that we replace the dtsrun commands with the dtexec one. Am I on the right track?
View 6 Replies
View Related
May 1, 2008
Hi,
We are in the process of converting legacy DTS packages to SSIS. we have some script like this :
If trim(DTSSource(col1)) = "" then
DTSDestination(DestCol) = null
DTSDestination(errorcode) = "1"
else
DTSDestination(DestCol) = trim(DTSDestination(DestCol))
I am re-writing like this:
trim(Row.col1) = "" then
Row.DestCol_isnull = true
Row.errorcode = "1"
else
Row.col1 = trim(Row.col1)
But, this is not writing NULL to the column, writing just nothing to the column. I want to see NULL in my columns. How do I achive this using script ?
Thank for the help!!
-V
View 1 Replies
View Related
Apr 9, 2008
if we see provider=sqloledb in the connection string of a legacy sql server 2000/2005 system we're trying to understand better, can we conclude that ODBC is definitely not in the client picture, that possibly OLEDB is and that SQL Native isnt? Can we conclude for sure what middleware must be in use?
My limited understanding of this "middleware" space is that ODBC is old and restrictive, OLEDB is a lot more state of the art and general, and that SQL Native is more proprietary than OLEDB.
Even if we can conclude what middleware must be in use, is it generally as simple as changing a connection string and ensuring installation of the preferred middleware in upgrading to better performing middleware?
View 6 Replies
View Related
May 29, 2007
Hi,
I am not able to delete the Legacy maintenance plans created:
for example I created a maintenance plan: "MyPlan" with:
master.dbo.xp_sqlmaint
and the plan created at:
ManagementLegacyDatabase Maintenance PlansMyPlan
After this I tryed to delete this using SQL Server Management Studio, it is
deleted (right click on the plan and select delete menu item)
But when I refresh the server, the "MyPlan" is back again.
Any idea how to delete Legacy Maintenance plans?
Thanks,
Venkat
View 4 Replies
View Related
Sep 13, 1999
Hi..,
There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome...
Thanks in advance..
View 2 Replies
View Related
Apr 11, 2008
I am unable to acces Oracle from legacy DTS designer, here is what I have done,
1. I have kept my old DTS packages in SQL2K5 legacy DTS container
2. Now I am unable to access oracle; it gives provider error
3. I am able to connect oracle from SQLPLUS
How can I troubleshoot this problem?
------------------------
I think, therefore I am - Rene Descartes
View 7 Replies
View Related
Jun 1, 2007
Hi all,
the SQL Management Studio keeps crashing out on me everytime i try to open a Lookup in one of my DTS packages i am using in the Legacy section.
I am copying the DTS package across and need to change the server connections (which i do), but then i was getting a permissions based error when the package ran when it tries to access the Lookup.
I tried to open the Lookup and the SQL Management Studio hanged when it tried to display the details for the lookup. It's done this many times and i have tried different files incase one was corrupted to no avail.
Anyone have any ideas of what i can try?
Thanks
Jordan
View 2 Replies
View Related
Sep 12, 2007
Hi Experts
I am trying to schedule a legacy DTS 2000 package in 64 bit Sql Server 2005.
I cannot use the DTSRun tool on the Command prompt of the Sql 2005. What about dtexec ?Can i run Sql Server 2000 DTS packages from the Command Prompt.
Is DTS 2000 the only way to do that?
Any Advise is greatly appreciated
Vic.
View 5 Replies
View Related
May 13, 2008
Hi All,
One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server..
At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
View 2 Replies
View Related
Nov 17, 2005
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
View 3 Replies
View Related
Oct 4, 2007
We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
Thanks
View 6 Replies
View Related
Mar 30, 2004
Let me see if I can explain this.
I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.
Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.
Step 2
The next tables data needs to be limited from the data retrieved in step 1 (I’d like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.
Step 3
The returned rows here, need to be limited to key values returned from step 2
Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.
What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.
I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.
What is the best approach?
View 2 Replies
View Related
Mar 20, 2006
I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is.
To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated).
On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :)
So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE")
A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices
B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field
C. If it's a new price it will insert a row into ItemPrices for that item
Also, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster?
Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much!
View 3 Replies
View Related
May 24, 2008
Hello all.
I use MDX on a cube which provides data about animal population.
The cube contains the keyfigure "ANIMALS" that takes the number of animals.
The cube has a dimension "VERSION" which is used to identify the keyfigure
as a target or an actual value (possible values: "actual" and "target")
The cube has another dimension "ZONE" for the population zones.
Possible values for zones: "A", "B", "C" and "D".
Now I want to create an MDX statement, that gives me a result row like this:
Actual number of animals (as sum of all 4 zones) in column no. 1,
Target number of animals (as sum of all 4 zones) in column no. 2,
Achieved percentage (as actual number / target number * 100) in column no. 3.
Until here my statement works and it looks like this:
WITH MEMBER [VERSION].[achieved] AS '[VERSION].[actual] / [VERSION].[target] * 100'
SELECT {[VERSION].[actual], [VERSION].[target], [VERSION].[achieved]} on COLUMNS
FROM [$MYCUBE]
WHERE ([Measures].[ANIMALS])
It surely is possible that the achieved value for all zones together is equal to or greater than 100%,
while single zones might have an achieved values less than 100%.
In order to account on this, i would like column no. 4 to display one of these words:
"ok" if none of the single zones has an achieved value smaller than 100%,
"warning" if any of the single zones has an achieved value between 96 and 99%,
"alert" if any of the single zones has an achieved value smaller than 95%.
That means, i want e.g. the word "yellow" if the lowest achieved value
of the 4 zones is between 96 and 99. I want to have "red" if the lowest value
is smaller than 95.
I am quite new to MDX and I have struggled quite a long time with this.
I would be grateful for a hint on how i have to modify / enhance my MDX statement.
Regards. Peter.
View 1 Replies
View Related
Sep 27, 2006
I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.
Source server SQL 2000 database allows NULL values in columns.
Destination Server also SQL 2000 but the database required a value in each column.
So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.
I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.
Maybe there is a better way. Suggestion and comment appreciated.
Ryan
View 1 Replies
View Related
May 18, 2006
Greetings my friends
I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package.
The package does the following :
1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table.
2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above)
3) Update the actual DIM table with the new data help in the staging table.
For this task I want to learn the use of the Lookup component which I think is appropriate.
My questions are as follows :
If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?!
I am totally confused... I don't even know where to start with this.
Your help would be appreciated.
Thanks SQL friends.
View 6 Replies
View Related
Jun 21, 2006
I've been banging my head for a while now, and it is sore! :-P
I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly.
I have a database, kind of like a forum.
Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance.
Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like...
"EditReply"
Edit reply takes the threadID, replyID and userID.
Before actually commiting the changes, it needs to check:
1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows)
2) does the thread exist?
3) does the reply exist?
if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table.
Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is
Am I doing this correctly? (no) - how can I improve this? What do I need to think about?
Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated".
In this situation, am I wrongly assuming that the database designers use this type of approach?
Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.
View 12 Replies
View Related
Aug 7, 2006
Hello,
I'm doing my best here, but need some help. I have a client that has a company list that they want searched by key word. This is exported from another program (in excel) that they want used and searched on their website.
Bad news, is each Keyword is listed with the company separately. So if a company has 5 different key words, they will be listed in the excel file 5 times.
The info I have is Name, Address, City, State, Phone, Keywords:
So example of excel is:Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Green
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Furry
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Large
Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Circular
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Blue
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Furry
Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Small
I am able to import this large (4.2 MB) file into a table called fctable
What i'm trying to do is write SQL scripts or queries that can insert into a Company Table and Keyword Table.
I'm trying to write this through asp.net 2.0 (so the excel file is uploaded) and have tried to write my inserts like
INSERT INTO [Company] ([Name], [address], [City], [State], [Phone]) VALUES (SELECT DISTINCT
Name, Address, City, State, Phone
FROM fctable )
But that doesn't seem to be working.
This is the only way my client can get the info to me, and it will be changed probably twice per month, so I'd hate to have to try to manipulate an excel file 24 times a year to import.
Any suggestions Appreciated
View 2 Replies
View Related
Feb 27, 2008
Hello everyone.
I am new to.Net and here is what I have to do.
I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone).
What would be the best approach to do this? What objects should I use?
Thanks a million,
Ben
View 2 Replies
View Related
Oct 18, 1999
Environment
NT Server 4.0 w/ SP4
SQL Server 7.0 w/ SP1
Win98 Client w/ Lotus Approach 9.5
I recently added SQL 7.0 to be a back end for my Approach front end. I transferred all the data from a dbase IV in approach to SQL. Most of the conversions worked ok. I have two big problems.
1) One particular repeat panel in Approach loses the children records of the master record. If I delete some of the records, more will appear. It's as if there is an imaginary limit of the number of records it can read in the repeat panel. I don't have this problem with any other records and children in repeat panels. I called Lotus and they don't have an answer. This is important because the children records need to be summed up so I can have a running total.
2) I original configured the clients to use the TCP/IP Netlib w/ the default port. I couldn't open enough databases so I changed to Multiprotocol. This allowed certain clients to open more databases, but others can't open additional databases. Also, after the change, the NT authentication login has had problems. I had to change to the SQL login to get all my clients back on line. Sometimes the same client can't open more than 10 databases while other times it will open 15. There is no consistent patten to when it can and can't open the additional databases.
If anyone knows how to fix either of these problems, I would greatly appreciate any advice. I'm getting tired of my boss yelling at me.
Thanks.
Keith
akumaboyz@aol.com
View 1 Replies
View Related
May 12, 2008
I have a select statement where I need to test two values that are returned and perform a different calculation if they return null.
Basically,
if TESTA is null and TESTB is not null
return TESTB
if TESTB is null and TESTA is not null
return TESTA
ELSE if both are not null
TestA + TestB / 2 is value is returned
is this possible to do in a select statement? thanks in advance.
See select statement below:
Select S.StudentDimKey,
(select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 1 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTA,
(select ISNULL(R.Score, NULL) from CLT_StudentAssessmentRawFact R, CLT_LessonPlanDim L where L.LessonPlanKey = 2 and L.LessonPlanDimKey = R.LessonPlanDimKey and StudentDimKey = S.StudentDimKey) as TESTB,
from CLT_StudentPlacementFact P, CLT_StudentDim S, CLT_ClassHierarchyDim H, CLT_StudentClassFact C
where P.StudentDimKey = S.StudentDimKey
and H.ClassHierarchyDimKey = C.ClassHierarchyDimKey
and S.StudentDimKey = C.StudentDimKey
and S.StatusCode = 'A'
and S.CurrentRecord = 1
and S.SchoolID = 87577
View 2 Replies
View Related
Feb 1, 2007
im practicing set based approaches... and what im trying to do is grab each value from a table , scramble it and put it back in the table... i dont want the solution to this as id rather figure it out myself for practice...
the thing im stuck at is i can do this with a cursor but i want to avoid cursors in future, how would i use a set based approach to get each value of a table and work with it?
View 4 Replies
View Related
