MS SQL Server :: Understanding What This Dts.Pipeline ERROR Means

What This Error Means And How To Solve It!!

Hi,

I have report server in SharePoint integrated mode and the Reporting services runnign under domain account. which is part of dmain adming group as well.but when i try to connect to http:\localhost
eportserver

it gives me this error:
Reporting Services Error

Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help

User cannot be found.

SQL Server Reporting Services

and in reporting service log it gives me following information
w3wp!webserver!1!8/15/2007-13:40:01:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: User cannot be found.
   at Microsoft.SharePoint.SPUserCollection.GetByID(Int32 id)
   at Microsoft.SharePoint.SPWeb.get_Author()
   at Microsoft.ReportingServices.SharePoint.Server.Utility.GetSPWebProperties(SPWeb web)
   at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.internalFindObjectsNonRecursive(String wssUrl, CatalogItemList& children)
   at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsNonRecursive(String wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.SharePoint.Server.SharePointDBInterface.FindObjectsNonRecursive(String wssUrl, CatalogItemList& childList, Security secMgr, IPathTranslator pathTranslator, Boolean appendMyReports)
   at Microsoft.ReportingServices.Library.ListChildrenAction.PerformActionNow()
   at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderFolder()
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType)
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()

how can I solve it!!!!!

thanks

View Replies !

Explain What This Error Message Means, Please....In Report Builder!
An error has occurred during report processing.

Query execution failed for data set 'dataSet'.

Semantic query execution failed. Too many table names in the query. The maximum allowable is 256. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

View Replies !

SSIS [DTS.Pipeline] Error
Hi I have created a simple SSIS project on my client that carries out 4 Data Flow tasks, each one copying a few hundred rows from an Oracle 10.0.2 database. This works OK and will also run in debug mode fine.

I have copied the package to the file system on our development server and get the following error when in debug mode:-

[DTS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Server.user" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Progress: Validating - 50 percent complete
[DTS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Validation is completed
[Connection manager "Server.user"] Error: SSIS Error Code DTS_E_OLEDBERROR. 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: "Error while trying to retrieve text for error ORA-01019 ".
Validation is completed

If you go to the source of each flow task and select preview you can retreive the data.

Thanks Paul

View Replies !

Iâ€™m Getting This Error When Checking Out A Report From Visual Source Safeâ€¦any Clue As To What It Means?
Originally got this error as the reason some of my .rptproj files could not be converted from SRS 2000 to 2005. Now I€™m getting this error when checking out a report from Visual Source Safe 6.0€¦any clue as to what it means?

Project item '4294967294' does not represent a file.

This is for a solution created and stored in VSS using VS2003 and SRS 2000. Now trying to open with VS 2005.

View Replies !

MS SQL Server 2005: Collect Procedure For &&"dts Pipeline Generate Error
Dear experts,

My MS SQL Server 2005 is generating the following error. may i know what's wrong with it?

"
The Collect Procedure for the "DTSPipeline" service in DLL "XXX:Program FilesMicrosoft SQL Server (x86)90DTSBinnDTSPipelinePerf.dll" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.
"

Thanks in advance for any assistance rendered.
pat

View Replies !

Understanding @@ERROR ....
My question is in what situations @@ERROR will be set...

I like to do some logic when some error is occured in a particular statement....

the doc. says the @@ERROR value will be set if an error occurs in a statement, and the control will move to the next statement without exiting(???) the procedure and @@ERROR value can be used in that statement.

but when i execute the below procedure, the execution is terminated ( when the error occurs) without moving to the next statement. please help me to understand the SQL Server's @@ERROR and the situations when it will be set....

-----------------------------------------------------------------------
CREATE PROCEDURE VALUE_ERROR_TEST
AS
BEGIN
DECLARE @adv_error INT
DECLARE @errno INT
DECLARE @var int
SELECT @var = '101 a'
SELECT @errno = @@ERROR
print @errno
END
go
-----------------------------------------------------------------------
procedure get successfully compiled. when executed it says,

Server: Msg 245, Level 16, State 1, Procedure VALUE_ERROR_TEST, Line 10
Syntax error converting the varchar value '101 a' to a column of data type int.

Jake

View Replies !

How Do I Write Multiple Pipeline Buffer To Multiple Targets Based On A Calculated Value In The Pipeline Buffer
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.

The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.

Any ideas would be greatly appreciated.

thanks

John

View Replies !

LDF Already Exists Error - Need Help Understanding
I'm developing an application using SQL Express that I plan to release for sale later this year.

Now that I'm in the testing phase, I am discovering that my application fails to create the database instance on several of the machines I'm testing on. The typical error I'm getting is the 'Cannot create the LDF file because it already exists'... however, I've also seen the 'Cannot create a user instance of this db' message as well.

I have read countless posts / blogs / etc on these errors and most of them seem to be relevant... however, after following some advice (like deleting the LDF and letting it get rebuilt... or providing a specific db name in my connection string) I'm still getting the same errors pop up from time to time. I even setup a fresh winxp sp2 virtual image to troubleshoot this error and after successfully repeating it in the virtual environment, somehow it started working all of the sudden.

I'm nervous about releasing an application without thoroughly understanding this error and making sure I've resolved it.

I'm using the default connection string that VS2005 makes when you attach a new database. I don't have it handy to post right now, but I would be happy to if it really matters.

My question is... in a product that I will eventually be charging for, is there some ideal way I need to write my connection string, or configure some settings in my project, in order to ensure that a sql 2005 express database can be used on any machine that downloads it? (I've already bootstrapped sql express into my installer).

Any help would be greatly appreciated. Thanks!

View Replies !

Understanding Error Handling In SSIS
I have a flat file component in a package that is reading a comma separated file.

It expects two columns of int type.

I have an on error event for the entire package. The On error event has one script task which just logs to the windows event log.

if I corrupt the input values in the file, for example instead of a number I put a character

When I debug the package, I see that the script task in the on error event blinks 7 times and I get 7 errors in the windows event log.

Now If I put another script task next to the flat file source and attach it to the flat file source as a error output (with row redirect).

I get only one error message.

Why are the two behaviours different? Both are error handlers one is at the component level the other is at the package level.

So why is it that the package level is invoked 7 times if the input is corrupt but the component level is invoked only once.

regards,

Abhishek.

View Replies !

Pipeline Error-excel Source-data Reader Does Not Read In Meta Data
Hi all, i got this error:

[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

and also this:

[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.

I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected.
It seems that somehow the 3 columns are not read in from the source file?
ans alslo fiscal year, fiscal week is not set up up properly in my data destination?
anyone faced such errors before?

Thanks

View Replies !

Just An Explanation Of What This Means Please
Hi,
I have a project that has been given to me and need help please. The complete class is as follows
Public Class CarAccessData
    Public Function Getcarinfo() As List(Of CarInfo)
        Dim AllCarInfo As New List(Of CarInfo)
        Dim SQL As String        SQL = "SELECT [SKU], [CarMake], [CarModel], [Carprice]"
        Dim MyConnection As SqlConnection        MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AntConnectionString1").ConnectionString)
        Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally
            aDataReader.Close()
            MyConnection.Close()        End Try
        Return AllCarInfo    End Function
End Class

The bit I dont quite understand is the following snippet from above

Dim aCmd As SqlCommand        aCmd = MyConnection.CreateCommand
        aCmd.CommandText = SQL        aCmd.CommandType = CommandType.Text
        Dim aDataReader As SqlDataReader
        Try
            MyConnection.Open()
            aDataReader = aCmd.ExecuteReader
            While aDataReader.Read()
                AllCarInfo.Add(New CarInfo(aDataReader))            End While
        Catch ex As Exception            Throw ex        Finally

Can anyone explain this in real ABC style step by step please, just so I can start to understand this, ( I am quite new to this)
many thanks,
Anteater

View Replies !

Instance Means...
In oracle The simplest description of instance : combination of memory and process architecture

I sql server, what is the Instance.? it is combination of memory and process architecture if yes then pls give name those processes ?

View Replies !

Cluster Means
Hi listers,
in cluster_algorithm is possible to extract the information of 'cluster profile' like mean and StdDev into a new table?

Any suggestion would be very appreciated.

Thanks in advance

Cristiano

View Replies !

Any Idea What This Means .....?

Hi,

Does anyone have any idea waht this means:

aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Reporting Services starting SKU: Developer
aspnet_wp!resourceutilities!5!07/11/2007-10:32:00:: i INFO: Evaluation copy: 0 days left

It is take from the ReportServer log file.

Thanks in advance for any response.

Jon

View Replies !

SP2 And BackupExec Means Failure
First off, congratulations and thanks to the SQL Server team!

Just thought I'd post a heads up to people running Backup Exec. I found out the hard way that BE does some stuff with files in the MSSQL Data directory. Essentially, it writes some files and only gives access to the BE account. This will cause the Database Engine not to upgrade.

Configuration is SQL 2005 Standard in a 2-node cluster. I believe the BE version is 10 but I'm not sure. I had this in the event log:

Product: Microsoft SQL Server 2005 -- Error 29506. SQL Server Setup failed to modify security permissions on file s:MSSQLMSSQL.1MSSQLData for user XXX. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that exists on the destination drive.

Going into the data directory, finding the BE files (they have $ in their names) and granting administrators full control allowed SP2 to install just fine. 3rd party vendors FTW.

It would be nice if the SP setup could do a quick permissions test before starting the install and taking SQL Server offline, but I suppose compensating for every incompetent vendor is a bit out of scope :).

(Yes some people mentioned this in SP1, and there's even a KB article (which doesn't name Veritas for some reason): http://support.microsoft.com/Default.aspx?kbid=916766)

Thanks,
Michael

View Replies !

Primary Key Means It Should Not Accept Null Values Is It?
Friends,

I am inserting a value from vb.net to sql server 7.
i created a table in the sqlserver with primary key.
In VB.net form i didnt check the values of the check box.
when i try to save the empty or null the sql server should throw the error. but its accepting the null value.
how is it possible?
can anyone help me?
FYI:
table name: JobMast
Fields:
jobcode int primary key
jobtitle varchar(10)

View Replies !

How Trustworthy Is Sys.partitions As A Means For Counting Rows In A Table?

Hi all,
I have the following function:

Code Block
create function udf_CountRows(@pTableName sysname)
returns int
as
begin
declare @ret int

select @ret = SUM(p.rows)
from sys.partitions p
inner join sys.objects o
on p.object_id = o.object_id
and o.[name] = 'Well'

return @ret
end

Can I trust sys.partitions to always return the correct value or does it suffer the same issue as sysindexes prior to SQL2005?

Thanks
Jamie

View Replies !

Pipeline
Hi,

I want to incorporate this code but I dont know how to import Microsoft.SqlServer.Dts.Pipeline in an Integration Services Project template. I was thinking of putting this code in the script task but still, I cant import Pipeline. Add reference list does not have it as well. Please let me know how to incorporate this code. Thanks!

Code:
if (ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager != null)
{
cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager);

if (cm.CreationName == "FILE")

{

fileUsage = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

if (fileUsage == Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

{

connectionString = ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager.AcquireConnection(transaction).ToString();

if (connectionString == null || connectionString.Length == 0)

{

throw new Exception("No file name specfiy");

}

}

else throw new Exception("Incorrect file connection usage type, should be set to exiting file type");

}

else throw new Exception("Connection is not a file connection");

}

else throw new Exception("Connection is not as assign");

View Replies !

What Does This Collate Keyword Means? - Praveen Pandey - CoVisible Solutions
Hello Members

What does "Collate" keyword means?

Regards
Praveen Kumar Pandey

CoVisible Solutions [I] Pvt. Ltd. - Software Outsourcing (http://www.covisible.com/cgi-bin/resources/software_outsourcing.asp)

View Replies !

Split Pipeline

This is probably obvious, but how do I split a pipeline. I.e. I've got a data source with 200 columns - I need to split this into 20 pipelines each containing 10 of the original columns.

View Replies !

Error: The Task With The Name &&"Data Flow Task&&" And The Creation Name &&"DTS.Pipeline.1&&" Is Not Registered For Use On This Computer

Hi,

I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.

Then when I try to delete it it gives this other error:

Cannot remove the specified item because it was not found in the specified Collection.

I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.

Any suggestions why this is happening and how to fix it?

View Replies !

SQL 2K5 SSIS DTS.Pipeline Errors
We have deployed an SSIS package successfully to production. We needed to apply SP1 to fix a different issue and now have encountered a new problem. We have numerous Data Reader Sources in different Data Flow Tasks that connect to a IBM iSeries (DB2) source. Pretty simple extracts that have worked fine in the past. They pump the data into staging tables on the SQL2K5 instance running the package (64-bit).

After we applied SP1 however, all of the Data Reader tasks fail AFTER they successfully copy the records with the following error.

[iSeries Invoice Details [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

If I delete the source and destination and recreate identical transforms, they work fine, but I don't feel like rebuilding all of the extracts. Any ideas! The problem occurs in all environments that we've tried.

TIA,
Michael Shugarman
P.S. I just tried the SP2 CTP, but that doesn't fix the problem.

View Replies !

Remove Duplicates Within Pipeline
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file. The tricky part is, that the record isn't entirely duplicated. What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ. So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary. Again, we can't tell at this point which of the duplicated records is more correct. Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS. If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

View Replies !

DTS.Pipeline.1 In SQL Server 2008
Hi

I have an existing application that programmatically builds SSIS 2005 packages.

I'm trying to get to working with the February CTP of SQL Server 2008. Having changed all the 2005 references to 2008 references and things like IDTSComponentMetaData90 to IDTSComponentMetaData100, my application compiles okay now, but hits a problem when it tries to create a Data Flow task.

The code which worked fine before (and seems to still be the recommended way in Books Online is):

Code Snippet

Dts.TaskHost myMainPipe = (Dts.TaskHost)container.Add("DTS.Pipeline.1");

However, this now produces the exception:

Cannot create a task with the name "DTS.Pipeline.1". Verify that the name is correct.

Should I be using a different moniker now? I took a stab at "DTS.Pipeline.2", but that didn't make a difference.

Thanks,
Andrew

View Replies !

DTS.Pipeline Information - Can I Access This?
Is there any way I can capture the below information? I want to capture this to get the no of rows processed by each transformation.

[DTS.Pipeline] Information: "component "abc" (3798)" wrote 2142 rows.
[DTS.Pipeline] Information: "component "xyz" (4223)" wrote 1026 rows.
[DTS.Pipeline] Information: "component "abc2" (4324)" wrote 7875 rows.

Thanks

View Replies !

Microsoft.SqlServer.Dts.Pipeline.BlobColumn
I am using Component Script to do - Transforming Comma-delimited list row data to column

and I want to use MessageBox to see the value

Dim DataPnts As String

DataPnts = Row.DataPnts.ToString() -- this is my input column (data type = text in Source table and I put as Unicode string [DT_WSTR] in Output column)

MessageBox.Show(DataPnts, "DataPoints1", MessageBoxButtons.OK)

---and why can't I see it. It gives me some message with Microsoft.SqlServer.Dts.Pipeline.BlobColumn. Why?

Values = DataPnts.Split(CChar(","))

Please point me to more info on how to do transform Comma-delimited list row data to column.

Thanks.

View Replies !

Logging In The Data Flow Pipeline
Hi,

Does anybody have any experience implementing logging within a data flow task? ie. logging timestamps for SCD component start and finish times.

Thx

tony

View Replies !

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException
Hi

I have a SSIS project that has one parent package and three child packages. When I run the project on my development machine in debug mode it works fine. Also if i run the packages using dtexec on my development machine it still works fine. However the problem comes in when I try and run the project using dtexec on the staging server i get the following error:

Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.

does anyone have any idea how to fix this please?

thanks

G

View Replies !

Would You Like The Ability To Hide Columns In The Pipeline?
Alot of people complain, legitamately, that they wish to remove columns from the SSIS pipeline that they know are not going to be used again. This would help to avoid the "clutter" that can exist when there are alot of columns in the pipeline.

If you are one of those people then click-through below, vote and (most importantly) add a comment. The more people that do that - the more likely we are to get this functionality in a future version.

SSIS: Hide columns in the pipeline
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252462

-Jamie

View Replies !

Intercept Pipeline Events Programmatically
Hello,

I'm wish to receive pipeline events fired by a SSIS package.

I execute the package successufully with the following code (c#):

MyEventListener eventListener = new XplorerEventListener();
DtsApplication app = new DtsApplication();
Package pkg = app.LoadPackage("c: est.dstx", null);
pkg.Execute(null, null, eventListener, null, null);

MyEventListener is inherited from DefaultEvents, overriding all OnXXX methods.

It works perfectly, however I cannot intercept the following events:

- PipelineExecutionTrees
- PipelineExecutionPlan
- PipelineExecutionInitialization
- BufferSizeTuning
- PipelineInitialization

Anyone knows how to catch those pipeline events?
TIA,
Paolo.

View Replies !

Retrieves The Information About The Pipeline Components
Dear Experts,

I can look the values of the proprieties in each PipelineComponentInfo, for example:

ComponentType: Transform
CreationName: DTSTransform.Merge.1
Description: Merge Transformation
FileName: C:Program FilesMicrosoft SQL Server90DTSPipelineComponentsTxMerge.dll
FileNameVersionString: 2000.90.1049.0
IconFile: C:Program FilesMicrosoft SQL Server90DTSPipelineComponentsTxMerge.dll
IconResource: -201
ID: {08AE886A-4124-499C-B332-16E3299D225A}
Name: Merge
NoEditor: False
ShapeProgID:
UITypeName: Microsoft.DataTransformationServices.....

but I don't know what means the proprieties: FileName, FileNameVersionString, IconFile, IconResource, NoEdit, ShapeProgID and UITypeName...

Can anyone helps Me?

Thanks

Francesco

View Replies !

SSIS DTS.Pipeline To MSAccess Databse

I cannot get a simple package to execute a data pump to an access database from SQL2005.
I have tried it in both SSIS and by running the Export data function.
I have been able to write to this database in the past using dtp in SQL2000 but I am not able to write to it using SQL2005.
What is the deal with the new SSIS?
Does anybody have any ideas I can try to get my export to work.
I have many more to do and I have to migrate over all of my SQL 2000 DTS packages to SQL2005 and some export to MSAccess.

This is the only error message I can find:
[DTS.Pipeline] Information: "component "OLE DB Destination 1" (2196)" wrote 0 rows.

Edit:
I found more errors in the debug section and a post here that discussed the problem as they had run into it. I was able to use part of that and some more research in order to tackle my problem.

I would still be interested in finding out why I suddenly had this problem arise after I upgraded to SQL2005.
This is going to be a real pain as apparently SQL2005 treats NULL as Zero Length and now all of my databsaes that had that set in access will have to be modified to deal with this in the export.

View Replies !

DTS.Pipeline: Validation Phase Is Beginning.
Hi, My package hangs and the log says DTS.Pipeline: Validation phase is beginning. Any ideas why this is happennig? This same package runs fine when I run it without turning on the transaction.

View Replies !

ReUse Common Surrogate Key Pipeline
I have several stage to star (i.e. moving data from a staging table through the key lookups into a fact table) ETL transformations in a single SSIS package. Each fact table has a different set of measures but the identical foreign key set, e.g. ConsultantKey, SubsidiaryKey, ContestKey, ContestParamKey and MonthKey.

Currently I have to replicate the key lookup (Surrogate Key Pipeline, or SKP) for each data flow. If I could cache each dimension one time in the package and reuse it for each stage to fact it would be much more efficient.

Is there a way for me to reuse a common data flow?

View Replies !

Small Necessary Changes Missing In SP2. Any Idea If This Is In Pipeline
1. i find it hard to believe that I cannot use stylesheets for textboxes or for that matter anything. I have to manually go and edit every textbox.
2. I have to define at t global level if a link opens in a new window or same window.

Anyone has ideas if this is even in the pipeline to be done?. I had asked the same questions on the release of SQL Server RS and that time it was thought that it would already be there in SP1/SP2. But i do not see it.

Or anyone has ideas how to achieve the above?

View Replies !

Transformation Object To Filter Data In Pipeline
Hi,
I have some data coming through pipeline and I wanna add some component at some point to pass on only selected rows based on conditions to the objects onwards. My opinion is I should use conditional split object, but Please suggest me something if you know better.

Thanks,
Fahad

View Replies !

How To Update A Dimension Column With The Pipeline Tasks
I have been working with DTS and ETL in data warehousing projects for several years and my question is this. You can only update a dimension column with SSIS by using TSQL-update statements.

There is no way to do this except issuing TSQL from the control flow or the data flow?

This subject is not mentioned in Wrox SSIS book nore in Kirk Haseldens book.

When you run the SCD task in the data flow you will get an OLEDB command that actually do this, issue a TSQL-statement.

Is this correct?

Regards

Thomas Ivarsson

View Replies !

[DTS.Pipeline] Information: Pre-Execute Phase Is Beginning
Hi,

I have a SSIS package which pumps data from one server to other without any additional steps. There are 11 tables for which data is transferred. And this packages runs fine on two different environments but fails in one environment i.e. on SIT.

It doesn't throw any error and every time stops at the below step

[DTS.Pipeline] Information: Pre-Execute phase is beginning.

Progress: Pre-Execute - 0 percent complete
Progress: Pre-Execute - 1 percent complete
Progress: Pre-Execute - 2 percent complete
Progress: Pre-Execute - 3 percent complete
Progress: Pre-Execute - 4 percent complete
Progress: Pre-Execute - 5 percent complete
Progress: Pre-Execute - 6 percent complete
Progress: Pre-Execute - 7 percent complete

It doesn't complete neither throws an error. Any pointers on what the problem could be

Thanks

View Replies !

Imports Microsoft.sqlserver.dts.pipeline Does Not Work
I have been trying to follow/implement the examples in the following help topics (thanks to Jamie for these links).

Building Packages Programmatically
(http://msdn2.microsoft.com/en-us/library/ms345167.aspx)
Connecting Data Flow Components Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136086.aspx)

The problem I am having is that MainPipe is not recognized as a valid type in my Script task, even though I have the imports statements that are listed in the example. I get the message "Error 30002: Type 'MainPipe' is not defined". The other and related problem is that when I type "imports microsoft.sqlserver.dts", the intellisense offers only two choices: {}Runtime and {}Tasks. I don't see any choice for Pipeline. Can anyone tell what I am missing? It seems to be some kind of configuration/installation issue, but I have no idea how to resolve it. I have tried this on 3 different machines, with both the RTM SQL 2005 standard edition, and with SP2 installed, all with the same result. Any help is appreciated

Here is my code:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.wrapper

Imports Microsoft.SqlServer.Dts.

Public Class ScriptMain

Public Sub Main()

'

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

New Microsoft.SqlServer.Dts.Runtime.Package()

Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

View Replies !

Adding Column Attributes For Custom Pipeline Component
I'm building a custom transform component. I want to mark some input columns as keys for deduplicating. In a similar way to the provided Sort component, I want to check those columns and allow pass-throughs (or not) for the others - so next to each input column name I need two checkboxes (1:use for dedupe; 2:include in output if 1 not checked). If a column is checked for use in the dedupe, I want some other attributes to be shown indicating how it will be used. How do I display the checkboxes to let users select which columns to include for deduplication, and then how do I add further attributes underneath (copying the Sort component's look) for selection?

Thanks in advance for guidance and pointers on this.

View Replies !

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Column Ordinal From Name?
Hi,

I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper.

My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me?

Dave

PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.

View Replies !

Help In Understanding This SQL...
Hi,

The following SQL is lifted from one of the Reporting Services / Adventureworks2000 sample reports. I'm a little slow / baffled on how the inner joins are working? Specifically the Inner Join Locale and Inner Join ProductModel. I'm used to seeing Inner Join SomTable On Something = Somthing but how these joins are working is lost on me. Can someone give a quick overview (or point me to a reference) so I can better understand.

Thanks!

SELECT ProductSubCategory.Name AS ProdSubCat, ProductModel.Name AS ProdModel, ProductCategory.Name AS ProdCat, ProductDescription.Description,
ProductPhoto.LargePhoto, Product.Name AS ProdName, Product.ProductNumber, Product.Color, Product.Size, Product.Weight, Product.DealerPrice,
Product.Style, Product.Class, Product.ListPrice
FROM ProductSubCategory
INNER JOIN Locale
INNER JOIN ProductDescriptionXLocale ON Locale.LocaleID = ProductDescriptionXLocale.LocaleID
INNER JOIN ProductDescription ON ProductDescriptionXLocale.ProductDescriptionID = ProductDescription.ProductDescriptionID
INNER JOIN ProductModel
INNER JOIN Product ON ProductModel.ProductModelID = Product.ProductModelID
INNER JOIN ProductModelXProductDescriptionXLocale ON ProductModel.ProductModelID = ProductModelXProductDescriptionXLocale.ProductModelID
ON ProductDescriptionXLocale.LocaleID = ProductModelXProductDescriptionXLocale.LocaleID AND
ProductDescriptionXLocale.ProductDescriptionID = ProductModelXProductDescriptionXLocale.ProductDescriptionID
ON ProductSubCategory.ProductSubCategoryID = Product.ProductSubCategoryID
INNER JOIN ProductCategory ON ProductSubCategory.ProductCategoryID = ProductCategory.ProductCategoryID
LEFT OUTER JOIN ProductPhoto ON Product.ProductPhotoID = ProductPhoto.ProductPhotoID
WHERE (Locale.LocaleID = 'EN')

Shawn

View Replies !

Need Help Understanding A Function
I'm trying to get the following poll working:http://www.codeproject.com/useritems/Site_Poll_Control.aspIt looks like it's exactly what I was looking for, but it doesn't come with much in the way of instructions. I have the following function: Public Function CastVote(ByVal PollId As Integer, ByVal Answer As Integer, ByVal MemberId As Integer) As Boolean Dim cmd As New SqlCommand("InsertPollResult", New SqlConnection(Connection)) With cmd.Parameters .AddWithValue("@PollId", PollId) .AddWithValue("@PollChoice", Answer) .AddWithValue("@MemberId", MemberId) End With Return (SqlExecuteInsertSp(cmd) > 0) End Function This calls SqlExecuteInsertSp(cmd) which is:Public Function SqlExecuteInsertSp(ByVal cmd As SqlCommand) As Integer Dim i As Integer cmd.CommandType = CommandType.StoredProcedure Try cmd.Connection.Open() i = cmd.ExecuteNonQuery() Catch ex As Exception ErrorMessage = "ProDBObject.SqlExecuteInsertSp(SqlCommand): " & ex.Message.ToString Finally cmd.Connection.Close() End Try Return i End Function I can't figure out what this is doing. The best I can figure is it determines if we have a good connection. Is this right? In my code CastVote keeps returning false, and I don't know why. The answer seems to be in the i = cmd.ExecuteNonQuery() line, but I can't figure out what that line is supposed to be doing.Diane

View Replies !

Understanding Triggers
Please consider the following example.CREATE TABLE test (an_ndx int NOT NULL primary key identity(1,1),a_var varchar(48) NOT NULL,last_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP);CREATE TABLE test_history (an_ndx int NOT NULL,a_var varchar(48) NOT NULL,last_edit_timestamp datetime NOT NULL,current_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP);GOCREATE TRIGGER update_history ON test FOR UPDATEASBEGININSERT INTO test_history (an_ndx, a_var, last_edit_timestamp)SELECT * FROM deleted;UPDATE inserted SET last_edit_timestamp = CURRENT_TIMESTAMP;END;The question is, does this do what I think it should do? What Iintended: An insert into test results in default values for an_ndx andlast_edit_timestamp. An update to test results in the original row(s)being copied to test_history, with a default value forcurrent_edit_timestamp, and the value of last_edit_timestamp beingupdated to the current timestamp. Each record in test_history shouldhave the valid time interval (last_edit_timestamp tocurrent_edit_timestamp) for each value a_var has had for the "object"or "record" identified by an_ndx.If not, what change(s) are needed to make it do what I want it to do?Will the trigger I defined above behave properly (i.e. as I intended)if more than one record needs to be updated?ThanksTed

View Replies !

Understanding Locks
If load a table using bcp or bulk-insert and use the tablock hint, do views or procs that have the nolock hint against the table work or are they locked out while the table is loading?

Thanks,

Steve

View Replies !

Understanding Sch-M Locks...
Hi Guys,
I have written quite a big stored procedure which creates a temporary table (multi-session) and updates it. All the statements are encapsulated in a single transaction which is explicitly declared in the code. What happens is that a lock is being put by the server on that table (of type Sch-M) in order thus preventing any type of operations on it (including simple select)

Now, I want to be able read that table from within another transaction. Why is that I cannot use a table hint NOLOCK in the select statement?

Here is some code which reproduces my problem.

Query A:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRAN TR_DEMO;

CREATE TABLE ##TBL1(
Oidx int not null primary key identity(1,1),
Name nvarchar(30) not null,
Type char(1) not null
);

INSERT ##TBL1 (Name,Type) VALUES ('Car','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES ('Plane','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES('Submarine','M');

WAITFOR DELAY '00:00:10';

DELETE FROM ##TBL1;

DROP TABLE ##TBL1;

COMMIT TRAN TR_DEMO;

Query B:

SELECT TOP 1 * FROM ##TBL1 (NOLOCK) ORDER BY oidx DESC;

Launch query A and then execute query B.

Thanks a lot for your help.

View Replies !

Threads - Need Further Understanding
I'm testing some scheduled jobs in a development environment.

I'm the only user on the SQL box (besides the SQLAgent account).

When starting (say around 6) separate scheduled tasks (Bulk Copy Tasks) two out of four indicate "Waiting for worker thread".

a. I know the "threads" are NT threads.
b. SQL has a setting "Max Worker Threads" defaults to 255
c. Other processes/services on my NT box are using NT threads also

1. How many "threads" does NT create?
2. What am I missing?
3. Does anyone know if other software (virus scan, etc.)
May be using a large number of my NT "threads"

It seems unlikely to me that with the 10 or less SPIDs running on SQL that I should be running out of threads. We're talking about under 10 users running an enterprise class server (Dell 4x500 4G Ram ~EMC SCSI 100GB)

I assume adjusting the Max Worker Threads will not affect how many Threads are available from NT, just whether SQL can use more. But how can my box be using the 255 threads (default setting) with only 10 spids running?

View Replies !