I have an embedded DTS 2000 package inside my SSIS 2005 Package. This DTS 2000 package just execute a transfer from a DB2 server to the 2005 server. Its a very simple transfer, without any transformation ( just column to column ).
Well, when I edit the DTS 2000 and save it, the package size increases about 100/200kb. It sounds like the package version ( the same as we have in the 2000 environment ). But i dont know how deactivate this "auto-increase".
Anyone can help me with this auto-increase? or just explain me what is happening?
Today I have packages with 9mb and just 3 or 4 embedded DTS 2000 packages inside.
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
On another thread a poster suggested that you can experience performance gains in Ssis by not using stored procedures.
I asked if I could have some Microsoft articles that articulated this position, but received no response.
Does anyone have links to Microsoft supported articles articulating when/if it is appropriate from Ssis to Not exec Stored Procedures for data manipulation or data collection?
I have successfully created a SSIS package which execute a DTS 2000 package and with no problem to execute the task. But I failed to schedule this package. I was not success in setting the logging. When running the package in command line:
dtexec file "C:Documents and SettingslyangMy DocumentsVisual Studio 2005ProjectsTraingDTSTraingDTSDTSTraining.dtsx"
Error: 2008-03-24 08:03:24.36 Code: 0xC0012024 Source: Execute DTS 2000 Package Task Description: The task "Execute DTS 2000 Package Task" cannot run on this edit ion of Integration Services. It requires a higher level edition. End Error Warning: 2008-03-24 08:03:24.38
Code: 0x80019002 Source: DTSTraining Description: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the M aximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1).
I have a performance related question about the DTS package in sqlserver 2000 which i have developed We have developed a DTS package which will migrate a view 'ATTRITION' from Sqlserver 2000 to an Oracle database.The design of the package is as follows First step: It checks for the existance of the table 'ATTRITION' in oracle database, if table 'ATTRITION' is not there it will create a table called 'ATTRITION' in the oracle db.If the table 'ATTRITION' is already present in the oracle db,then the table is truncated.
Second step: The view 'ATTRITION' is migrated to Oracle table 'ATTRITION'. For the migration, i have used a connection object which connects to sqlserver 2000 and for oracle connection i have used another connection object 'Microsoft ODBC driver for oracle' and i have joined both the connection objects with 'Transform data task' task which maps one to one from sqlserver 2000 where view 'ATTRITION' exists with oracle database where Table 'ATTRITION' exists. Roughly i have around 65000 rows in 'ATTRITION' view of sqlserver 2000 which needs to be migrated.When im running the package on my system it takes around 4 minutes to migrate all the rows but when im running it on the server it takes a lot lot of time more than 1 hour.
The view definition im using has more than 10 tables joined together.But if its a problem of query used in the view,and if i run the view seperately it quickly displays the data hardly takes 1 minute. and even if i run the package on my local pc it doenst take much time.Now my confusion is why its taking soo much time on server.If i create a indexed view then will it solve my problem.Please suggest... Thanks in advance
I have a DTS package that transfers some columns defined as varchar(8000).
Sometimes, the values in the columns defined this way have embedded CR/LF in them. When they do, DTS does not always transfer the values to the target.
I'm looking into a problem a friend is having, and I'll say right offthe bat that I work with with php and MySQL, and not MS SQL.What he is attempting to do (in MS SQL) is take two database fieldsfrom a table (string fields), multiply them together, and put them intoa third field. This third column in the table has not yet been createdthe time of running the query.If it needs to be multiple queries, that is fine. My first thought isto use a simple ALTER query to add the column to the table, then tocall a UPDATE function which uses a select statement inside of it. I'mnot sure if something like this can even be done.// ------------ Suggested queryUPDATE chrisslu SET 'discquantity' = '(SELECTchrisslu.quantity*chrisslu.nr_of_discFROM chrissluWHERE (str(period,6)>=? AND str(period,6)<=?))' WHERE(str(period,6)>=?Andstr(period,6)<=?)// ------------ End Suggested queryIt starts with an UPDATE, but replaces the value to be set with aSELECT statement. I honestly don't even think this query issyntactically correct, I'm just trying to get the general concept down:).So, question the first: Is this type of query possible? The reasonI'm doing this is because I was told MS SQL has no way of storingtemporary variables... otherwise I would just call a SELECT statement,store the variable, and UPDATE the new field from the variable afterthe ALTER statement.Second question: If it is possible, am I on the right track, or doesit need to be entered in completely different than what I have?Third: Regarding the 'type'. Do I need to do any kind of typecastingor conversion of the fields? Both chrisslu.quantity andchrisslu.nr_of_disc are string fields (that is what I was told, theymay be varchar of some kind). In order to use them in a mathstatement, do they have to be floats, or doubles, or something similar?I appreciate any response, I know this was a long winded question.Chris
Is there any way to embed a video in a report? It doesn't seem like it but I thought I may be missing something. Maybe directly editing the xml code? Thanks.
I have embedded tabs in a text field that I want to import to a destination table.
I was thinking I need to replace the tabs with spaces. REPLACE(character_expression,searchstring,replacementstring) Anybody know how to specify ascii in the character expression.If there is a better way I am open to suggestions, however I do not way to remove this in the raw data but handle at transformation time. Thanks,Larry
I've an embedded image (logo) in my report, and in the preview it looks wonderful, but after deploy the logo doesnt appear in the report (report server).
The url of the dead-link-image is http://hamsql1/Reports$BI/Reserved.ReportViewerWebControl.axd?.
I thought that an embedded image is stored "in" the report, but it seems to be an other place. Is it nessecary to deploy an embedded image too? Is it a problem of permissions (i read it in another threat)? How to set these image-see-permission? Or is there an other problem?
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.
I am running a DTS package that runs a T-SQL statement and every once and a while I get the following error: 2007-11-19 01:40:20.16 spid54 Error: 0, Severity: 19, State: 0 2007-11-19 01:40:20.16 spid54 SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.. 2007-11-19 01:40:23.54 spid54 language_exec: Process 54 generated an access violation.
From MS SQL Server Management Studio 2005, I register and connect to MS SQL 2000 (SP4) running on another server. I can see the list of 2000 DTS packages on the other server, but when I try to open them I first get:
Error Source : Microsoft Data Transfformation Services (DTS) Package
Error Description : The DTS host failed to load or save the package properly.
I click the OK button and get:
The selected package cannot be opened. The DTS Desiger has been closed.
I click this OK button and get to now famous:
SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)
I have already been to the Feature Pack for Microsoft SQL Server 2005 - November 2005 page and installed the Microsoft SQL Server 2005 Backward Compatibility Components and the Microsoft SQL Server 2000 DTS Designer Components.
Now, the DTS Designer does not close. It stays open. I can see inside the package, but all I see are the transformation lines and the precedence lines. The icons are invisable. If I click on the invisable icons, at the end of the lines, an icon appears. This is not a normal icon. It looks like little windows with a blue border and title bar. Nothing I've seen before. Oh it gets better. If I now double-click the icons, the properties window appear as normal. Looks good... I don't need to change anything. I then press the OK button and get the Task references, like I modified the connection. I do not clear the trasformation. I just click the OK and like magic the connection icon looks at it should. This works on everthing I've tried, except for Execute SQL Task icons. I can save it, but not as the save name. I can open the new saved package. It look and works fine.
I am looking for a way to execute an SSIS package as the next step in the SQL 2000 Agent, with the first step being replication. Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?
I have a Select statement that was working just fine: string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; I am completely lost here.
I am trying to find something like a light version of SQL Server 2k to store data for an application that will be distributed through a cd.
Since we also have a web front end for this application when the user is online, we prefer that we can reuse the code for connection on both the cd and the web.
Any idea what will work?
I have come accross MSDE 2k but was not able to find any documentation on the pros and cons for this product regarding its capacity, the type of data it can handle, security, ...
I was wondering if anyone had tried to embed sql server express in a .net application. Specifically, I want to use it from within the app only, similar to embedded mysql (libmysqld) or firebird. I've seen some articles on sql server express saying this can be done, however there is no info on how do to this. Anyone know how it might be done?
Beyond my control: I am finding control characters (likely tab) ismaking its way into address fields of our operational system. This ismessing me up when I load the data into our warehouse w/ BCP (fieldsget shifted).Is the a nifty way to strip control characters from data?TIARob
What I'd like to do is use the result of one query as the input foranother. eg:INSERT INTO foo VALUES ((SELECT id FROM people WHERE name = "bar"),10,'foobar') WHEREid = 1;Problem is, MSSQL wants a scalar value. Is there a way around this?Can someone tell me the correct syntax for what I want to do, or is thissomething that will have to be done outside SQL?Err. Hope I've been clear. Thanks for any help you folks can give.
We have text files that are comma delimited, use double quotes as text qualifiers and sometimes have embedded double quotes. The embedded double quotes are escaped with an additional double quote like: below.
"123","product q"
"124","product ""a"""
DTS 2000 had no problem with this- it correctly parsed the files. The 2005 SSIS file connection manager correctly parses this in preview mode. But when the task is executed the task fails with the message "The column delimiter for column X was not found".
What is the recommended approach for this - we have alot of files in this format.
I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.
How can I fix the location of textboxes that renders at one location all the time ?
documentation states, that Windows XP Embedded (XPe) is not supported by any SQL Server 2005 Edition (as I intend to use the Epress Edition on that platform, I post to this forum). So, why is that? Is it just not tested or are there technical reasons for it? What are these reasons? What works and what doesn't on XPe? May XPe be supported in any later version?
Has anyone been able to run a DTS package from DOS. When I execute the dtsrun command it fires the process off in another Dos session and quickly closes. I know that SQL 7.0 worked ok. When it runs from dos it tell you when each step runs successfully or fails. I am Running Windows 2000 Server SP2 and SQL 2000 SP1. So the question is, is there any way to run the dtsrun utility from dos so you can see what is going on?
Hi All, My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code. The brief history of issue is : We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine. Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .
Can you please help me out to get the proper syntax to call the DTS package from VB code.
I've tried several different possibilities but don't have enough basicunderstanding of the ownerships and permissions that are involved soI'm looking for someone to explain what is required:I created a DTS package that includes a drop table, create table, anddata transformation task. Originally, my owner was local to the pcworkstation with administrator priveleges. When I executed thepackage on demand (manually, not scheduled), it runs just fine. Noerrors. When I schedule the package to run at a scheduled day/time,it fails with an access is denied message.I originally was using the sql admin user and password in theconnection but the view job history kept referencing "Executed asuser: sqlservernameSYSTEM...." and errored with an access is deniedmessage. Where did it get the SYSTEM user from since my connectionused the sql admin user in my DTS package? Do I need to determine howto get the sql admin user or should the SYSTEM user really have to runthis scheduled job? How do I get the sql admin user to show as the"executed as user"?I created another admin user to see if it had someone to do with thesql admin account but I got similar results. I tried creating thepackage as an NT domain user but got similar results.I'm sure it has something to do with permissions and ownerships but Ireally don't have a clear understanding of how it was intended towork.Any advise you can give is greatly appreciated. I've read alot ofpostings and I'm still not clear on the requirements.
Dear friends, can we do this? converting a package made in ssis 2005 to dts packages of 2000? or we can somehow make the same working on sql server 2000? any ideas?
We just did an in-place upgrade from sql2000EE to sql2005EE and I installed the SQL Server 2000 DTS Designer componets but I don't see the option to schedule a package.
Hi i have a video file path stored in my sql database and i have an embedded media player in my web page, how can i load up a different video file into the embedded media player based on movie id or movie name, this is the code i have written so far but im unsure if im in the right direction, thanks
I am trying to modify a piece of SQL where I want to get the date difference from todays date and the datestamp on the latest record on dbo.activitym1 table AC1 ..which matches the following criteria..WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) PS1.number is the master record ID But this doesnt seem to work ..the best result i've been able to achieve is all records from the dbo.activitym1 table that match the where clause and i only want the latest by date heres the code any help would be most appriciatedSelect PS1.number, PS1.brief_description," + " DateDiff(day, PS1.open_time, Getdate()) as 'Days_Open', PS1.company," +" (select DateDiff(day, AC1.datestamp, Getdate())" + " From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.activitym1 as AC1" +" WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) as 'Days_Since_Last_Assigned'," + " CONVERT(VARCHAR(10), PS1.open_time, 103) as 'Date_logged'" + " From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.probsummarym1 as PS1" +
"where (PS1.assignment = 'PROD - UK CENTRAL CPH COGNOS SUPPORT' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH CONS HEALTH' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH PUBLICATIONS' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH TERRITORY PLANNER' OR " + " PS1.assignment = 'PROD - UK CENTRAL CPH VIEWPLUS' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL CHEMICAL PIONEER' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY PROFILES' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY SEARCH' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL GENERIC PLNG & ANLYSER' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE NPF' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE PATENTS' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE RDF' OR " + " PS1.assignment = 'PROD - UK CENTRAL EDITORIAL MIDAS GENERICS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING ANALOGUE' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING GENERICS MARKET PROGNOSIS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING MARKET PROGNOSIS' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING OTC REVIEW' OR " + " PS1.assignment = 'PROD - UK CENTRAL FORECASTING THERAPY FORECASTER' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CHEM INTPACK' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CLASSFN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CORP' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB CUSU' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB DSG' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB GPIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB INTPRD' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB KNOWLEDGELINK' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB LICENSING' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB LPIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB MARK SEG' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB OTHER' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB PRICES FACTORS' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB STANDARDS' OR " + " PS1.assignment = 'PROD - UK CENTRAL GLOB WHO' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT ACCOUNTS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT ADMINS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT EXEC' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA CODING' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA CPMS' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA PROD CTRL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT HPA QUALITY CONTROL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT INTL FIELDWORK' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT IT SOL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PANEL CTRL' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA CODIN' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA PROCESSING' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA QRY & SUPPORT' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PRODUCT REFERENCE' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT PSO' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT QUALITY ASSURANCE' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT RESEARCH HOSPITAL GROUP' OR " + " PS1.assignment = 'PROD - UK CENTRAL SIT WARD' OR " + " PS1.assignment = 'PROD - UK CENTRAL SITTINGBOURNE' )" + " and UPPER(PS1.Status) <> 'CLOSED'
