Un-Pivot transform crashes BIDS (Serious Issue)

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - index replication from DB2 I misread your posting... I think that's the question for DB2 Replication forum. Regards, Gary Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 2
    - Flat file to table - rows out of order There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes. Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things. These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected. There is no concept of order in a database table. Period. -Jamie Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 3
    - SQL Server install That could be permissions related covered below by Microsoft and Partners. http://www.iis.net/articles/view.aspx/IIS7/Deploy-an-IIS7-Server/Installing-IIS7/Install-IIS7-on-Vista http://msdn2.microsoft.com/en-us/vstudio/aa972193.aspx Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 4
    - stored procedure parsing comments incorrectly For a second time I encountered a strange problem with a stored procedure, which is simply not parsing and executing correctly. On both occasions the problem occurred with pretty long stored procedures - the one I have now is 2000 lines long. The first time I saw this I was working on SQL Server 2000, now I have a SQL Server 2005, but the database is in SQL Server 2000(80) compatibility level. The problem is that a regular SQL comment is sometimes merged with the line under it. --some comment some SQL code is parsed as --some comment some SQL code You can see how this can be a huge problem. From what I know the contents of a sp are stored in the system table syscomments, which splits the longer stored procedures into several records. I am wondering if there isn't a parsing problem when putting together a longer stored procedure. Perhaps if a "new line" character happens to be at the records split it is ignored and the two lines of TSQL get merged. My "fix" has been to readjust/remove the comments, add additional empty rows in between or just split the stored procedure into several smaller sps. I will appreciate your feedback if you know more about parsing the stored procedure from the database engine or if you have a better work-around. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 5
    - Using XML Source in SSIS vs Script Source to parse xml files. Hi I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins. so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation. i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins. Hari Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 6
    - Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime It works. I did like this! SELECT @state = STATE , @theState = CONVERT ( char ( 20 ), STATE_TIMESTAMP , 20 ), @id = ID , @filename = FILENAME FROM DELIVERY WHERE SUBSTRING ( FILENAME , 21 , 4 )+ '-' + SUBSTRING ( FILENAME , 25 , 2 )+ '-' + SUBSTRING ( FILENAME , 27 , 2 )+ ' ' + SUBSTRING ( FILENAME , 30 , 2 )+ ':' + SUBSTRING ( FILENAME , 32 , 2 ) between @theEndTime and @theCurTime ORDER BY ID DESC ; END I want to use SUBSTRING but start count from the end of the string. Anyone know howto Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 7
    - Radio button By custom applications I meant WinForms or web-based applications you write. You can use the ReportViewers controls to render reports in custom .NET-based applications. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 8
    - Transfer object task error. Hi, i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB. i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description. [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". Thanks, Haroon Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 9
    - MIgrating Existing WorkFlow Data using SSIS Hi, I have posted the same query in the WF group too. Thought I'll post the same here. We have an existing application which has some WorkFlow Data. We need to migrate this data using SSIS. Now the problem here is that we have our Workflow facade class which inturn calls the WorkFlow Runtime. We have created the methods which will create the new WorkFlows. TO add this DLL in our SCript Component Reference, we added a strong name to my Facade.dll and deployed it to GAC. The system.WorkFlow DLL's are also present there. Everytime I run this Package, I get the following error "System.IO.FileNotFoundException: Could not load assembly...". What am I missing Thanks Soni Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 10
    - OLAP Error - Excel Pivot I was wondering what service packs are you guys running for both SQL2005 and AS2005 Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 11
    - Cannot connect to SQL Express from WEB Application on internet Hi, I have installed SQl express 2005 SP1 and then configured it to accept remote connections which works within my internal network. I can connect other client stations within our internal network. However, when i try to connect to the database using a VB.net web application, the application cannot connect to the database. I have a hardware firewall which configured to foward port TCP 1433 and UDP 1434 to the SQL express server. I have also carried out a telnet communication to ports 1433 and 1434 and able to communicate on port 1433 and get an error message cannot connect to host on port 1434. What am i doing wrong Thanks Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 12
    - plain text mails How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 13
    - The Device is not ready error when Executing package on the server Jennifer LaMacchia wrote: I have the same problem. I am able to successfully map a drive but as soon as I attempt any file manipulation I get the "device is not ready" error message when running from SQL Scheduled job using xp_cmdshell or if running from SQL Management Studio in a query window. The commands work fine outside of SQL in a regular command window. Have you tried using the UNC form of the path \\server\path\file Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 14
    - Unable to install SQL Server 2008 on Windows Vista Hi, After some tries, I could manage installing the client tools as well. But you have to first uninstall the SQL2005 client tools. Because clients tools are not supported for a side by side installation. So I uninstalled the workstation components of sql2005 then restart and then finish the process by running the \tools\setup\sqlrun_tools.msi You can read my post on http://www.kodyaz.com/blogs/software_development_blog/archive/2007/06/10/567.aspx for how I completed the installation of Katmai Client Tools I hope this helps you too. Eralper Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 15
    - Query Plan in SQL Server 2005 SP2 What you describe is exactly my situation. I have NO, REPEAT NO, control over the SQL query which gets generated by the report writter. The user's can select any number of values for the IN clause. Some work, some don't. To MS, I would point out the EXACT same query runs in 10 seconds on SQL 2000 SP4 and I cancelled the query under SQL 2005 SP1 after 1 hour. So it is definitally a problem with SQL 2005. This needs to be fixed. I have no work around, except the have the user select less items and run the report multiple times. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 16
    - How to Locate the SSIS Installation? Part of our product will be using several SSIS packages. One of our installers wants to be able to create a .cmd file that will run DTEXEC against one of the packages, with the correct config file specified. It had been assuming that SQL Server was installed in the Program Files special directory, but that's not always the case. What is the correct way to locate the installation of SSIS In fact: What is the correct way to determine whether SSIS is installed What is the correct way to determine the version and SP that is installed (I want to require SP2) I had been using the values under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup until I found an SP2 machine with SSIS installed which did not have these keys. Where's the right place to look Tag: Un-Pivot transform crashes BIDS (Serious Issue)
- Next
  - 1
    - Transform collate fields Is there someone to give me an answer Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 2
    - Msg 137, Level 15, State 2, Line 8 / Must declare the scalar variable "@Interest". Hi guys! I have written the following store procedure in SQL server express 2005. But I get the above result when I parse it. Anyone for a tip Thanks Jean-Philippe CREATE PROCEDURE procInsertClient -- Add the parameters for the stored procedure here @ClientID smallint OUTPUT , @FirstName nchar ( 20 ), @LastName nchar ( 20 ), @Phone nchar ( 15 ), @Email nchar ( 30 ), @Interest text , @Newsletter char ( 3 ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; INSERT INTO ClientHeader ( FirstName , LastName , Phone , Email ) VALUES ( @FirstName , @LastName , @Phone , @Email ) END GO BEGIN INSERT INTO ClientDetails ( Interest , Newsletter ) VALUES ( @Interest , @Newsletter ) END GO Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 3
    - logistic regression model formual hello , i am using neural network model to build logistic regression how can i see the formula of the model the paramters that came out and their coefficiant i have look at the model content but couldn't find it tx Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 4
    - How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out. I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body). Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method. Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution). Thanks. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 5
    - How to format cell / data apprearecnce under a table Hello All, I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g it should be : firstname lastname however the format appears very strange: firstname lastname firstname lastname fistname lastname Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay. I will really appreciate any sort of help on this one. Thanks, Rashi Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 6
    - SSIS Script Task I can't tell exactly where the error is from your description. Try downloading the sample package from the URL I posted above, and make sure that is working for you. Then change it (step-bystep) to meet your needs. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 7
    - Data Conversion Question I am using the instructions at the following link to create an update/insert load: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx My input data has values that are 8 bit signed integers DT_I8. My output table uses BIGINT for the same fields. Now I know that the conversion in SSIS is BIGINT = DT_STR. The question I have is this. On the INSERT side of the OLE DB Command I did not have to convert the DT_I8 values to write to the table. HOWEVER on the UPDATE side, the update errored out UNTIL I did a data conversion to DT_STR of the same fields. Why is it different between the UPDATE side and the INSERT side Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 8
    - How to flush data stored in SSIS buffer Hi, I am working on my own data flow source component. Here is a fragment of this component code: public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers) { PipelineBuffer selectedBuffer = buffers[0]; string message; while ((message = GetMessage()) != null) { selectedBuffer.AddRow(); selectedBuffer.SetString(0, message); // how to flush data here } selectedBuffer.SetEndOfRowset(); } private string GetMessage() { // we are retrieving some message here, this is a long-term process } When a new row is added by this component to the buffer then this row is not immediately available to the next component in data flow. It is possible to configure SSIS in that way that each row is immediately sent to the next data flow component If no also please inform me about that. Thanks, Rafal Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 9
    - How to hide URL when display reports in reporting services Hi , How can I hide the URL information when I open a report, some with parameters and some do not have parameters. Is there anyway or properties in RS that I can set to have it hide all the important information, like datasource, credentials, parameters, and display the report like a aspx page instead Or does this need to have special programming to make this happen and if so, what do I need to do thanks very much Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 10
    - Question on calculated members Hi, PedroCGD, Thanks a lot for your kind advices. It's been very helpfu. With best regards, Yours sincerely, Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 11
    - Cannot start SQL Server Browser Thank you Matt for your help. I tried the "netstat -aon -p UDP" command. The Windows 2000 Server does not seem to support the -o switch which shows the Process ID. I could only do "netstat -an -p UDP", without the "o". I can see that 1434 was listed, but do not know the process ID. How may I proceed Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 12
    - Multiple Database Files: Good or neutral? I have inherited some responsibilities for which I'm not really qualified, so I'll push on through and maybe not totally fall down. Assume 10 50GB databases, each in a single MDF file. All these MDF files reside on the C drive (the only drive on the system), running SQL 2005 in a 32-bit Windows 2003 or later, 8GB RAM. The C drive is 6 physical disks in RAID 5, say about 1.0 TB or so. We have 4 dual-core processors on the box. We have limited simultaneous users, initally about 8 users doing very heavy write on all tables in any one database. Later, we have about 15 users connecting via Web interface, and doing very heavy read and light writing. Each of the 10 or so database has this lifecycle: Heavy write for about 2 weeks (load data) then heavy read for about 1 month (research and search data), then nothing ever again (db is taken offline). Of course, this is not enough information to go on, but let's just go on it anyway. My TempDB, Log (simple recovery), Index etc is all on the same RAID 5 drive (C). I have two basic questions I'd love to hear feedback on: 1. Is there any real advantage to creating 8 Data files for my database (one per processor core) 2. Given that the hardware people here REALLY don't want to change anything, what should I fight for first: a. Separate drive for LOG files b. Separate drive for TempDB c. Something else Thanks in advance. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 13
    - ntext columns truncated to 32K when processed by Integration Services yes. it is the same ODBC driver in both. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 14
    - BCP to a table partition Looking for bcp utility [SQL Server], performance in BOL i read: "Where possible, use a Transact-SQL statement to bulk import data into SQL Server because Transact-SQL is faster than bcp ." so maybe you can consider using bulk insert command. Read "About Bulk Import and Bulk Export Operations." that is an item that show a comparison from bulk insert methods from performance point of view. I think the SQL engine move the data in appropriate partition not bcp; the overhead can be saved by the power of the machine. Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 15
    - junk character within FLAT FILE Antony Kumar wrote: Thank you. How to remove this ASCII by using "Derived Component" or "Script Component" Pls provide the sample code. Thank you Use an escape character within a REPLACE function. There's a list of most (perhaps all) the escape charactes that you'll need here: http://msdn2.microsoft.com/en-us/library/ms141001.aspx e.g. If it turns out that the character is a tab your derived column expression will look something like this: REPLACE([column-name], "\t", "") -Jamie Tag: Un-Pivot transform crashes BIDS (Serious Issue)
  - 16
    - SQL Server Licensing I have a three server network. One SQL Server, One Web Server, and a backup. The web server is ASP.NET, and uses the database. I do not track the number of users hitting the web server. What type of sql server license is needed Processor Device CAL User CAL I have read all the white papers and licensing info and can't find an aswer to this question. Please help Tag: Un-Pivot transform crashes BIDS (Serious Issue)

Dharmbir

I am facing a serious issue is BIDS.

I have configured a Un-Pivot transform, source to unpivot is sort, when I change the source to derived column (one column name is same for both) and try to open Unpivot it gives me unreferenced lineage id (this is correct). Now When I select Delete Unreferenced Lineage Column and select Apply , it works till here. As soon as I select OK, BIDS crashes and closes.

Re: Un-Pivot transform crashes BIDS (Serious Issue)

Rafael Salas

This may not be what you want to hear; but have you tried removing the Unpivot component and add it back

Re: Un-Pivot transform crashes BIDS (Serious Issue)

Dharmbir

yes ..and it works. But BIDS crashes each time in the above scenerio

Re: Un-Pivot transform crashes BIDS (Serious Issue)

Rafael Salas

You mean it works fine after you removed and added back the component please provide the details on how you fixed and marked the thread as answered. That would help other people having similar issues

Re: Un-Pivot transform crashes BIDS (Serious Issue)

Dharmbir

Rafael,

The original issuse was not solved.

But I found two work arounds.

1) Delete the component and add component again..too much of work

2) Map the unreferenced column to new column coming from derieved column manually.

and then it works fine