Answer Questions

• MikeLR Main = DTSTaskExecResult_Failure does not stop package

  Hello, I created an ActiveX Script task in SQL Server 2005 checking the record count of a table. I want the package stop to run and not to continue next task(s) if the record count is not 1. The related part of the code is as the following: ..... If mySourceRecordset.RecordCount <> 1 Then MsgBox " Model dates are different, stop here.. " Main = DTSTaskExecResult_Failure Else Main = DTSTaskExecResult_Success End If ...... However, although the record count is not zero, and it gives the error message in the MsgBox statement above, It gives a green color for this task and continues to run the following tasks in the package. What can be the problem causing it not to stop the package Could an ...Show All

• Lawrence Parker SSIS - Connection String Issue

  Hi I have been trying to resolve a connection string issue all day, and havent figured out whats the problem. I keep getting the Acquire Connection error everytime I run my package. Can you someone review the format of my connection string, and confirm that it is indeed the correct format: I have two conn strings specified in my config file, one is to connect to a remote database and one is for local. I have validated the connectivity from my server to these databases via Management Studio, and it seems to connect fine. But when I run my package it keeps failing. SQL Server Authentication(Remote Database Server): Data Source=server_name;Username=useraccount;Password=xxxxx;Initial Catalog=dbname;Provider=SQLNCLI.1; Window ...Show All

• Mike Riley Performance problem: SSAS 2005 + ProClarity

  Hi, I am facing a performance problem. Here is my scenario: I am using ProClarity to create reports/graphs by connecting to ssas 2005. These are not dynamic graphs but are created beforehand in proclarity and accessed by users. The graphs enable drill-down/drill-up features. My Cube contains: 1 MeasureGroup 4 Measures 7 calculated measures 4 dimensions ( 1 time dimension: date) dimension partition count: 1,095 4,018 8 453 4,018 (hierarchy) 11 (hierarchy) 7 (hierarchy) 5 hierarchies in time dimension 1 hierarchy in other dimension 1 partition, partition count: 33,574 (current) partition size : 708.4 kb storage: MOLAP Partition aggregations: 15 current performance gain: 51% This cube is processed manually and appx of ...Show All

• jack-n-jill How to move Transactional Distributor to new platform?

  I have to move our dedicated Sql 2000 transactional distributor to a new and faster machine. Great! Can someone give me or point me to a cookbook of the best way to do this This has to be done quickly so I would like to script it and have it ready ahead of time. I am not a replication guru so I am a bit nervous . . . I have looked at the scripts output by EM, but I am sure there are some assumptions . . Thanks I think to move a distributor you have to remove replication altogether. ...Show All

• Will8754 Maintenance Plan Changes in SQL Server 2005 SP2

  Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools. Who is affected You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected. What is the issue Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response ...Show All

• V1ad How to impletement this query in SSIS package

  Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table update <input dataset> set column01 = -1 where column01 not in (select column from dbo.columnlist) and column01 <> -1 Perhaps a combination of derived columns, conditional statements, and merge joins (perhaps). Jeff_LIU wrote: how could I impletement "Not in (select column from columnlist )" in a lookup component a lookup component can only use equal join. Right, which is why I said to use the error output. For every input row that isn't in the lookup, it'll go down the error output (the red arrow). how could I impletement ...Show All

• KingKoo Date out of range?!

  Greetings! I have a data source that gets generated based on a variable with the following SQL : "select * from result where deletion_ind = 1 and when_deleted >= '" + (dt_str, 50, 1252) @[User::Last_Run_Date] + "'" But when I run my package I get an error message saying: The conversion of CHAR to DATETIME resulted in a DATETIME value out of range The Last_Run_Date variable is set to '2006-06-25 14:35:05.450' When I run the code in a QA session it works, but in the package it complains! What am I doing wrong Thanks for your help in advance. Donald is referring to the locale if your client workstation on which SSIS is installed. However, this is a murky area. You should ...Show All

• waheyluggage Combine raw files using range

  HI, I have a dataflow that has two raw files as source and I would like to merge them upon a range condition: RawFile1.Date <= RawFile2.Date Usually, using tables, I would have used a lookup with partial cache to achieve it. Now, since we cannot use lookup transform with raw files, I was wondering how I could achieve this using raw files as source. Is it possible to merge raw files using merge or merge join Thank you, Ccote ccote wrote: Thank you both for your inputs. I will have to revise the design of this package. I wanted to get rid of the table solution since SSIS is installed on an application server (different than SQL server). By using raw files, I would be able to do all ...Show All

• TA123 How can I shrink SQL Express database on local machines?

  Hello, I have deployed a vb.net application to a few laptops that holds the SQL Express database as a local datastore. What is the best way to compress or shrink the database and log files I would like a way that this can be done automatically or unattended if possible... Thanks in advance. Rashar wrote: Jens K. Suessmeyer wrote: YOu could create a job (like an at job) on the the machine to execute the procedures for shrinking the database or the logfiles. Another option would be to use some kind of logic within your application code to shrink the database from time to time / at startup. (Look for DBCC SHRINKDATABASE in the BOL) HTH, Jens K. Suessmeyer. ...Show All

• Peter Lillevold SSIS problem after installing 2153 hotfix

  The Kb article says to install all component patches, even if you are not running them, to avoid errors...the reporting services hotfix would not install(obviously since reporting svcs is not installed).....now I get the following erro, when trying to connect to the SSIS on my sql server...oh, I am trying to connect locally on the server it self too.... Cannot connect to CRP-DAY1-SQLs1. =================================== Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) ------------------------------ For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Smo.Enumerator.Process( ...Show All

• maurocam Using Stored Procedures with Report Builder

  New to the forum here. I'm having a trouble. My company is contemplating using Report Builder as an end user tool as opposed to giving everyone a copy of Visual Studio. I'm studying the feasability on this. A problem I'm running into, is that it doesn't seem possible to use a stored procedure as a data source. All of our standard reports are based off of stored procedures. Now granted, I'm talking about just Report Builder. I know how to base a report off of a SP using reporting services in VS. So, my question is, can you create a report off of a SP using only Report Builder, and if so, the steps Thanks guys! Bueller......Bueller..... Thanks Bob, I appreciate the feedback. Oh well, I guess we'll have to go another route. ...Show All

• tovarish inserting data for testing

  This is the code I'm using to insert data into a table with data from one table, but changing a single column to prevent duplicates and to increase the number of rows. I know I'm missing something easy, but... what is it thx, Kat code to create the table: CREATE TABLE [OrdDetails] ( [OrderID] [int] NOT NULL , [ProductID] [int] NOT NULL , [UnitPrice] [money] NOT NULL , [Quantity] [smallint] NOT NULL , [Discount] [real] NOT NULL , CONSTRAINT [PK_OrdDetails] PRIMARY KEY CLUSTERED ( [OrderID], [ProductID] ) ON [PRIMARY] ) ON [PRIMARY] GO I inserted all the data from the [Order Details] table, now I'm trying to reinsert the data but changing the PK constraint to prevent error. Seems like this should work. truncate table Ord ...Show All

• RichieD123 Compare csv file layout to SQL Server table

  I am creating a facility whereby you can select a source file (.csv), and a target (SQL table). Then I call a DTS to copy the csv file to the SQL table. How can I validate that the two files have the same number of columns I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application. In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package. You can write a custom Script Task inside the SSIS package that will open the fi ...Show All

• Steve Wenck Forward looking thought: Raw Files

  I assume that MS has a directive never to change the format of SSIS raw files... However, what I'd like to know is that when I'm planning long-term systems where I've got backups of data (staging, logging, whatever) using raw files, can I be assured that future versions of SSIS will be able to read those raw files I assume a certain level of backwards compatibility, however, I'm just curious if I should think about building processes into my projects that would factor that in and rebuild raw files everytime a new/major release of SSIS comes out. Phil bump We may change the format of SSIS raw files in any version. If we do, then we will ensure that the previous version can be read – but we will not make a continuing guarantee ...Show All

• Nilesh Ingale merge replication corruption (system triggers and views)

  All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating: Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated. Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to ...Show All