Web Services And SSIS

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Recordset.update with SQL Native Client doesn't work We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy. You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright. Is this a bug Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary. Any insights would be greatly appreciated. Best regards, DD Dim ilinx As New ADODB.Connection Dim rsPorder As New ADODB.Recordset Dim rsPorder2 As New ADODB.Recordset Dim cmdLinx As New ADODB.Command Dim strConn As String Dim lvIli_number As String 'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=MicrosoftR Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;" strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=MicrosoftR Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;" ilinx.Open strConn Set cmdLinx.ActiveConnection = ilinx lvIli_number = "12345" cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' " cmdLinx.CommandType = 1 rsPorder.Open cmdLinx, , 1, 1 If rsPorder.EOF Then cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' " cmdLinx.CommandType = 1 rsPorder2.Open cmdLinx, , 1, 3 If rsPorder2.RecordCount > 0 Then rsPorder2("Locked") = 0 rsPorder2.Update End If End If rsPorder.Close Tag: Web Services And SSIS
  - 2
    - Is there any sample code to demo the SSB send messages with same sql instance? Is there any sample code to demo the SSB send messages with same sql instance my case is very simple: I want write a stored procedure to send a xml to another database. The stored procedure is called by tables triggers when some data is changed under the specific conditions. Tag: Web Services And SSIS
  - 3
    - 64 bit reporting services with 32 bit wss 3.0 Hi all, I'm trying to find out if there will be any problems if I have a 64 bit Reporting Server integrating with a 32 bit Wss (3.0) server farm Thanks. Nestor Tag: Web Services And SSIS
  - 4
    - Not able to install SQL Server 2005 Hi All, I have a problem again. i am trying to install SQL Server 2005, but on the verge of completion of the set up, it says " the set up encountered an error and SQL Server 2005 was not installed" (This is not the exact error message though; it meant this). It ultimately ends up installing only the configuration tools. Though all the services and groups are created, the DB engine is not to be seen. What could have been the problem The server has AMD processor with 3 GHz and 8 GB of RAM. I also happened to read that SQL Server 2005 does not support AMD processors, it that true Thanks a lot... Mannu. Tag: Web Services And SSIS
  - 5
    - Maintance plans corrupted after sp2... Posted - 03/19/2007 : 09:14:02 Tag: Web Services And SSIS
  - 6
    - Could not set "Ignore Failure" for OLE DB Destiantion component I am using Microsoft SQL Server Integration Services Designer Version 9.00.1399.00. My OLE DB Destiantion component is inserting data into a table. When there is duplicate it will failed. But I want ignore this since I know data is same. But even I set Ignore Failure from OLE DB Destioantion Editor. It will not work and every time you reopen editor, the buttom drop down box always showing as " Fail componet". When I try run it, it will always fail on the inserting duplicated rows. Does anyone know how can I tell servies ignore this error. Thanks! Tag: Web Services And SSIS
  - 7
    - SSCE Transaction Log Hi, Are there any pecularities of Transaction Logs in SSCE comparing to the same log in full scale SQL Server How are they stored And when they are considered to be expired in order to safely delete them Thanx in advance Tag: Web Services And SSIS
  - 8
    - Creating Dynamic dimension Security with Custom Procedure Hi All, We are trying to implement dynamic dimension security using a Custom procedure written in C#. If we create a single entry in the return set everything works fine (See Below) Microsoft.AnalysisServices.AdomdServer. Expression Expression = new Microsoft.AnalysisServices.AdomdServer. Expression (); Expression.ExpressionText = "[Sub Branch Id].[All Branch].[4]" ; Microsoft.AnalysisServices.AdomdServer. Member Member = Expression.CalculateMdxObject( null ).ToMember(); TupleBuilder.Add(Member); SetBuilder.Add(TupleBuilder.ToTuple()); However when we try and add a second item to the Tuple builder the object returns the error : The 'Sub Branch Id' Hierarchy appears more than once in the tuple. Code snippet : { Microsoft.AnalysisServices.AdomdServer. Expression Expression = new Microsoft.AnalysisServices.AdomdServer. Expression (); Expression.ExpressionText = "[Sub Branch Id].[All Branch].[2]" ; Microsoft.AnalysisServices.AdomdServer. Member Member = Expression.CalculateMdxObject( null ).ToMember(); TupleBuilder.Add(Member); Expression= null ; Member= null ; } { Microsoft.AnalysisServices.AdomdServer. Expression Expression = new Microsoft.AnalysisServices.AdomdServer. Expression (); Expression.ExpressionText = "[Sub Branch Id].[All Branch].[3]" ; Microsoft.AnalysisServices.AdomdServer. Member Member = Expression.CalculateMdxObject( null ).ToMember(); TupleBuilder.Add(Member); Expression= null ; Member= null ; } SetBuilder.Add(TupleBuilder.ToTuple()); Please can some one help resolve this issue as its becoming critical for the project that we are currently working on, otherwise we will have to revisit the security model. TIA Tag: Web Services And SSIS
  - 9
    - Invalid Character in Flat File or Turncate problem My source is a csv flat file. Currently I use that same flat file on SQL 2000 and SQL 2005. On SQL 2000 it runs fine and it inserts that character as part of the string (varchar), however, it gives me truncate error on sql 2005. I already use the "Suggest Types...." and my Output columns have the correct lenght (specially that lehght of that column is only 30 char which is less than the default anyways). If I remove that character it runs fine for that column..... This is the values that I get in my flat file for the trouble coulmn is "ATTN: JON OLSEN a€“ CTRL8 " And the error that I get when running the SSIS is [Flat File Source OrderDetail [1]] Error: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". [Flat File Source OrderDetail [1]] Error: The "output column "ShipToAddr1" (63)" failed because truncation occurred, and the truncation row disposition on "output column "ShipToAddr1" (63)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. [Flat File Source OrderDetail [1]] Error: An error occurred while processing file "C:\Inetpub\ftproot\orderdetail.csv" on data row 6. Any help greatly approciated..... Thank you, Maria Tag: Web Services And SSIS
  - 10
    - Checksum transformation is not detecting my change I am using the Konesans Checksum transformation ( http://www.sqlis.com/21.aspx ) to detect changes in my big (many columns, type 2 SCD) dimensional table. But I am running into collossions The checksum transformation, sometimes misses a small change in the record, for instance when a certain flag is set or unset. Is there a more robust checksum generator Of any other suggestions on to solve this thx Tag: Web Services And SSIS
  - 11
    - Hiding a table row depending on page number? Hi, Everyone. i am wondering if there is some way to hide a table row depending on the page number. I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful. Tag: Web Services And SSIS
  - 12
    - SQL Server 2005 remote connection failure I'm having a big problem with a SQL 2K5 cluster (using standard edition). I can get onto the box and connect to the sql server locally fine. However when I try to connect remotely I get errors. Let's call the two sides of the cluster, CL1 and CL2 and the "virtual server" is CL3. Locally I can connect without any problem at all to the SQL server, but I just can't connect to it from any remote machine. The server in question is instance name LMS_PRD running on CL3, port 10051. TCP/IP is definitely enabled for local and remote connections (I have checked this in the SQL server surface area configuration). I can connect locally without any problem. Also, locally, if I do, telnet CL3 10051 it connects fine From a SQL 2000 box - when I try to connect from sql query analyzer, I get "Unable to connect to server LMS_PRD Server Msg 17 ... SQL server does not exist or access denied" Client network utility shows server alias - LMS_PRD, TCP/IP, CL3\LMS_PRD,10051 [I can connect to other SQL 2005 servers OK from this same server using this same method] On this box, when I do telnet CL3 10051 , I get "Connecting to CL3...Could not open a connection to host on port 10051 : Connect failed" [When I do a similar one for another SQL 2005 box, "telnet U01 10051", it connects fine] From another SQL 2005 box - when I try to register the server, I get "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP provider, error 0 - No connection could be made because thew target machine actively refused it.) (Microsoft SQL Server, Error: 10061)" IN the SQL native client configuration it shows alias name - CL3\LMS_PRD, server=CL3\LMS_PRD, protocol tcp, parameters 10051 Again if I do telnet CL3 10051, I get the error above. From this same box I can register another SQL 2005 server without any problem and do the telnet to it OK Just to emphasise: (a) The SQL server configuration manager shows that TCP/IP is enabled, and that 10051 is the port (in IPALL, TCP port). Also I have checked the SQL server errorlog - it is definitely using 10051 as the port. (b) The SQL server surface area configuration shows under local and remote connections using both TCP/IP and named pipes selected (I've tried both this and using the tcp/ip only option) (c) SQL server browser is running (d) There are NO firewalls etc involved Any help or advice MUCH appreciated Tag: Web Services And SSIS
  - 13
    - ads wizard: Failure to open SQL Server with given connect string Hi, sorry but I am just getting my feet wet with the ADS wizard. I have managed to get the PPC emulator running the ADS wizard and have the emulator ActiveSync connected with desktop. Port 1024 opens from the emulator to the desktop. On the ADS wiz I can connect to the device .sdf. WHen I try to connect to the desktop I am getting a connection failure in the ADS wiz log and after stepping through the code VS watch window gives the following message for the SQL exception: Message "Failure to open SQL Server with given connect string. [ connect string = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.accdb; Jet OLEDB ystem Database=C:\\Documents and Settings\\vhaporellinr.POR-EE778886\\Application Data\\Microsoft\\Access\\system.mdw ]" string I do not know enough to know where to go next to move past this no-doubt simple problem. Can you direct me to the appropriate docs or refs to start digging into looking for solutions Is there a particular Northwind.mdb file that is supposed to be used Are we talking some kind of permissions issues on the database I was assuming I did not have to install SQL Server 2005 and the SQL Server management console( ) to do development with the SSCE. Is that correct THank you for any helping pointers you can provide. Tag: Web Services And SSIS
  - 14
    - Address field problem. I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly Tag: Web Services And SSIS
  - 15
    - Oracle connection fail with Microsoft OLEDB provider for Oracle MSDAORA.1 Hello, On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider. I use one or the other according to my needs. In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev. Almost everything works except Microsoft OLEDB provider for Oracle. ssis packages on the test machine will return an error Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009. Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. [Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.". I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine. Does anyone could point me to the right direction to solve this issue Thanks, Philippe Tag: Web Services And SSIS
  - 16
    - Populate flatfile source from variable We are storing incoming flatfiles into a text field in a table and then we are processing this table on a regular basis. What I would like to do is to get this flatfile from the textfield and populate a flatfile source with it, but so far I have only been able to do that with XML files as there are no option for doing that with the flatfile source. Using the disk as a temporary storage for the flatfile is prohibited. Does anyone have any suggestions on how to solve this Tag: Web Services And SSIS
- Next
  - 1
    - Sql Server 2005 not starting Hi, I have my web application with backend as Sql Server 2005 CTP(Yukon). Initially It works well. I updated some security settings to the OS Windows Server 2003 and restarted the machine. Now the Sql Server is not starting.When I manually started It logs error as follows. 2005-10-13 03:39:57.85 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered. 2005-10-13 03:39:57.85 Server Registry startup parameters: 2005-10-13 03:39:57.85 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 2005-10-13 03:39:57.85 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 2005-10-13 03:39:57.85 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 2005-10-13 03:39:57.85 Server Error: 17113, Severity: 16, State: 1. 2005-10-13 03:39:57.85 Server Error 3(The system cannot find the path specified.) occurred while opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary. 2005-10-13 03:39:57.85 Server Error: 17053, Severity: 16, State: 1. 2005-10-13 03:39:57.85 Server UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered. dinator (MS DTC). This is an informational message only. No user action is required. In the event viewer I noticed the following error Message 1&2. Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a. 3. Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log. 4. Event ID 17120 : SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems. Can anyone help me Thanks in Advance, Raj Thilak Tag: Web Services And SSIS
  - 2
    - Package taking 100% of the CPU when it is opened for editing in visual studio I have a package that I was able to edit a week before. But now it is consuming all CPU memory (100%) and not letting me to edit the package (When I try to edit that it says Visual Studio Is busy even after an hour waiting). Even though I have not changed anything, the package is behaving like this. I would appreciate any reply on this Thanks in advance Tag: Web Services And SSIS
  - 3
    - Import text file to SQL Server I have a project which I need to do the following steps: 1.Look for a partular file .txt in a directory. If the fil exists, loadn txt file ino SQL Server. 2. If # 1 was sucessful, then delete the source file. Every day, do the same thing ie check for existence of file, import into sql table, then delete the file. Pl advise the best way to do this: Tag: Web Services And SSIS
  - 4
    - Restore sql server 2000 backup on sql server 2008 Based on the books online I am not allowed to upgrade a database at sql server 2000 to sql server 2008. But I tried restoring the sql server 2000 backup on the Katmai release available and it works. It initially did have the database in sql server 2000 compatibility mode but later I was even able to change the mode to be 2008 compatible. So what is it am I allowed to do it or not Tag: Web Services And SSIS
  - 5
    - NON_EMPTY_BEHAVIOR if NON EMPTY isn't used A cube I'm working on has lots of calculations. But none of the MDX queries in the reports use the NON EMPTY clause or the NonEmpty function. What I'm wondering is whether the optimizer or any other part of the Formula Engine uses NON_EMPTY_BEHAVIOR for anything else besides removing empty cells from the resultset. If the Sum function is summing two calculated measures, will a NEB on those two calculated measures help the Sum function, for example That's the kind of thing I'm looking for... places where NEB is used other than removing entire cells from the resultset. Tag: Web Services And SSIS
  - 6
    - XQuery: Error converting data type nvarchar to numeric. I am trying to run a query off of a xml data type using Xquery (nodes() and value() methods) but am running inot a conversion error for a numeric type in the xml which is empty or blank. I want it to come back as null but it looks like it is trying to convert an empty string. Is there any way that an empty tag could pull back a null Tag: Web Services And SSIS
  - 7
    - ClusterDistance value Can somebody please define the result of the ClusterDistance() function for me. BOL and MSDN are vague about what the output means and I am getting some cases that have a value of zero and sometimes there are more than one per cluster. Thanks in advance, Dan Meyers Tag: Web Services And SSIS
  - 8
    - SQLSTATE 42000- Error 15404 with ADS Hello, I'm having trouble running jobs with my active directory (ADS) account. I've setup my SQL services to run under an ADS account, but jobs cannot seem to query ADS for user information. We're running Windows Server 2003 and SQL Server 2005 SP2. Here is the error message: == The job failed. Unable to determine if the owner (ADS\me) of job eFASRtest has server access (reason: Could not obtain information about Windows NT group/user 'ADS\me', error code 0x5. [SQLSTATE 42000] (Error 15404)). == also this message in log: == [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'ADS\me, error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin) == I already tested the suggested: execute as login='ads\me' and I get the same error on both (my local installations and production) appreciate your help Tag: Web Services And SSIS
  - 9
    - Help with installing/setup of SQL Server 2000 Trial I've installed SQL Server 2000 trial on a standalone XP Pro machine as a local server, however the debugger does not work correctly. I will not allow me to step into the code of stored procedures. Can anyone provide me assistance in resolving this. Tag: Web Services And SSIS
  - 10
    - How to select the "Select All" checkbox parameter? When passing the prameter values to a linked report, I want to set one of the parameter at the linked report to "Select All", this was added automatically to the parameter list when multivalue report parameter is used . How to set this "Select All" as a parameter or can it be done Thanks Tag: Web Services And SSIS
  - 11
    - Trigger in the Subscription database - Transactional Replication... Hi, We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1. Subscription Type : Pull Subscription Mode : Continuous Running Mode Conflict Resolution Policy : Publisher Wins. I have a table " Sample " (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION . The first trigger Updates a column of the "Sample" table in which i inserted a record. The second trigger inserts record in to another database table and also updates a column of the table "Sample" . The third trigger does not affect any tables, it is written for some manipulations with variables. In this scenario when I insert a record in the Sample table of the subscription database , that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also. But when I comment any one update in either the first or second trigger, the insert works fine without any conflict. Is there any issue with firing two triggers in replication which is updating the same table I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser. Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id It would be helpful if u could reply soon. Thanks and Regards, Swapna.B. Tag: Web Services And SSIS
  - 12
    - Dimension Hierarchy does not allow duplicates Hello, I am very new to data warehousing. I have created a hierarchy on "StoreID", "Make" and "Model". The Make "Harley" should appear below all of our Harley-Davidson store ids. It looks like there are no duplicates of any make or model in the hierarchy when I browse the dimension. I am using BI dev studio and SQL 2005 analysis services. I'm guessing it's just some property I have not found yet. Any ideas Thanks Tag: Web Services And SSIS
  - 13
    - UniqueIdentifier error in SSIS Hi, I got problem when i am sending the data from source to destination. My source is Sql Server 2005 and My Destination is Sql Server 2005. In Source table the column name is defined as numeric datatype. In Destination table the column name is defined as UniqueIdentifier. When i send the data from Source to Destination i am getting this error. [Data Conversion 1 [4190]] Error: Data conversion failed while converting column "Req_seq1" (3959) to column "Copy of Req_seq1" (4204). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Tag: Web Services And SSIS
  - 14
    - SharePoint on SQL Server 2008?? Hi Is it possible to run SharePoint 2007 on a SQL Server 2008 Is there any information/documentation about this issue Thanks /Charly Tag: Web Services And SSIS
  - 15
    - Report Header woes I am creating a report which consists of data in two columns. I've set the Body section to have 2 columns in it. This works fine until I start working with the report header. I do not want a page header that shows up on every page, I only want a header on the first page, a report header. I first tried to hide the header by placing it in the Page Header section, then putting in the "page > 1" expression in the visibility property. This does what I want, except a large blank space is present at the top of my report (the header is about 1.2 "). This is unacceptable. So my next test was to put the header into the Body section so that it only shows up in the beginning of the report. Because of this, I will need to remove the columns from the Body and make it only 1 column now. I decided to put the multi-column section into a subreport that has 2 columns and add this to the body of my report. But, this failed; the data appears in one column only. I can change the width the ensure it fits and set all other sorts of properties, nothing works. I've researched both issues and found that these are both "bugs" in reporting services, although I cannot find thedocumented bugs. Is there any workaround to putting a header onto only the first page of a multi-column report Other reporting engines are able to handle this with ease, I'm suprised by Reporting Services lack of a "Report Header" section. I am an experienced .NET developer, so if there is a programmatic way through this, I am game. Thanks, Scott Steigerwald Tag: Web Services And SSIS
  - 16
    - RemoteDataAccess Dear Friends, To use RDA, do I still have to use Replication, Publishing if yes which publication need to be used is it Transaction If you can guide me as to how I can imliment RDA, would be a great help. Tag: Web Services And SSIS

JamFisk

I am having trouble executing the example from microsoft that call a DTS package from a web server. I alway get Service Failed.

I have loggin on in the pagage and it doen't get that far. I have set up IUSR_<machine> with access to sql server and granted approiated file permissions. The package loads but fails on execution. I can run the same code as a console app and it works. Any help would greatly appreciated.

Top

Re: Web Services And SSIS

Jamie Thomson

JamFisk wrote:

I am having trouble executing the example from microsoft that call a DTS package from a web server. I alway get Service Failed.

I have loggin on in the pagage and it doen't get that far. I have set up IUSR_<machine> with access to sql server and granted approiated file permissions. The package loads but fails on execution. I can run the same code as a console app and it works. Any help would greatly appreciated.

You say DTS but this is a SSIS forum. Can I clarify whether you asking about DTS or SSIS.

It would help to link to said example as well.

Thanks

Jamie

Top