Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Add A Script Component Using C# Code??


Hi all,

Could you please provide sample code to add a SCRIPT component and LOOKUP component using C# code? Also, is there any reference site other than msdn to learn about SSIS programming?

Regards,

Gopi


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Serious Script Component Bug - Clears Out All Code Inside Component
 

No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
 
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
 
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
 
I then close my package  / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
 
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
 
I can reproduce this on 2 different computers.
 
Anyone experience this problem ?  Any idea how to stop it ?  Or debug it ?

 
Here is a slimmed down code sample of what causes the error :
 
 
Public Class ScriptMain
 Public Sub Main()
        Try
            Dim xmlDoc As New XmlDocument
            xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
            MsgBox("xmlLoaded")  --this doesn't display once the package starts "acting up"
        Catch ex As Exception
            MsgBox(ex.Message)
            UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
        End Try
        Dts.TaskResult = Dts.Results.Success
    End Sub
    Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
        Try
            Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
            Dim rd As StreamReader = New StreamReader(fileName)
            Dim xml As String = rd.ReadToEnd()
            Xml = Xml.Replace(invalidChar, String.Empty)
            xml = xml.Replace("", String.Empty)
            xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
            xml = xml.Replace("]]>]]>", "]]>")
            MsgBox("replaced")
            rd.Close()
            Dim wr As StreamWriter = New StreamWriter(fileName)
            wr.Write(xml)
            wr.Close()
            Dim xdoc As XmlDocument = New XmlDocument()
            xdoc.Load(fileName)
        Catch ex As Exception
            UpdateXML(fileName, ex.Message)
        End Try
    End Sub
End Class

View Replies !   View Related
Using VB Code In Script Component
Hi,

Is there anyways I can access VB code from my script component?

In other words, is there anyway we can refer VB dll from SSIS Script Component?

Thanks,

S Suresh

View Replies !   View Related
Code Sample For SSIS Script Component
Greetings,

 

I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA.  Now we are moving all of our data to SQL Server 2005.  I am in need of learning how to write code for the Script Component of a data flow task.  And so I have a couple of questions.

 

First, are there any books you recommend for learning ???? (I'm not even sure what I need to learn:  .NET?  ADO.NET?)

 

And as a follow-up, any good websites that provide good reference documentation?

 

And my second question is more specific to my current problem.  If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill. 

 

Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID.
I need to read each row and compare it to the next row.  If STATUS is the same, discard the second row.
When STATUS is different, send the first row to the output (to be used by the next task in the data flow). 
Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements.  I'm just not understanding how to read the rows in and then send them back out.  I've been searching for some sample code but everything I find tends to be solving much bigger issues.

 

Any help you can provide would be much appreciated.

 

Rob

 

View Replies !   View Related
Script Component - Code Not Saving After Edit
I have SQL Server 2005 Developer Edition. I have a script component in one dataflow which I have added code to today. I saved using the save Icon and exited. I noticed a warning on the item, so I went back into the script and my changes had gone. I though I could not of saved the code and retyped, saving every 5 minutes. I closed visual studio, and noticed the script component had the warning on again! I went back to the code and all my changes were gone. I did not see any errors or warnings in Visual Studio. What am I missing, retyping code becomes tedious!

View Replies !   View Related
Script Component Can't Find The Binary Code
Dear all:

          I have set the PrecompileScriptIntoBinaryCode property to true,but the Script Component show the message "can't find the binary code" after I finish the script language and save it . What's wrong with it ? I ever tried the same code, and it's OK with no problem. When did the problem happen ?

         Please give me a help ....Thanks a lot!

         

View Replies !   View Related
The Product Level Is Insufficient For Component When Executing Package Using C# Code.
Hi,

I have created a Integration Services package that takes a table in a database, and transfers it to a flat file. This package has successfully run through visual studio 2005 as a .dtsx package, and given the output that I expected.

However, now, I am trying to excecute the package (as xml) using C#, and I am receving this error:

Error in Microsoft.SqlServer.Dts.Runtime.TaskHost/DTS.Pipeline : The product level is insufficient for component "Flat File Destination" (31).

I do not understand how a working package would have this kind of error.

Considering that it runs when I do not use C# code to execute the package means that I have SSIS properly installed, and I have the proper versions (or it should not execute ever). I have SP1 for both SQL Server 2005, and Visual Studio 2005 installed.

 

Other packages that I have created using C# code also have the same problem.

 

Any assistance would be greatly appreciated.

 

View Replies !   View Related
Script Destination Component Code Sample Here That Creates A Csv Or Xml File
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.

On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"

The readonly User: variables that start with gs can be read in the PreExecute method

The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow

The xml code is adapted from an idea in Donald Farmers book

enjoy

Dave

Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat!
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
 
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
 
Public Class ScriptMain
    Inherits UserComponent
    Dim sw As StreamWriter
    'In addition to using the Imports System.Xml statement a reference must be added to the
    'System.Xml assembly  (Select Project-Add Reference from IDE)
    Dim xWriter As XmlTextWriter
    Dim OutputFileType As String '.csv or .xml
 
 
 
    Public Overrides Sub PreExecute()
       
        'Read Only variables
        Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid'
        Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
        Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
        Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
        Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
        Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
        Dim fileName As String = gsPickUp & "" & gsPickUpFilename
        fileName = fileName & (Format(Now(), "yyMMdd").ToString)
        'MsgBox(fileName)
 
        OutputFileType = gsPickUpFileExtn
        If OutputFileType = ".csv" Then
            fileName = fileName & gsPickUpFileExtn
            sw = New StreamWriter(fileName) 'connection to dest file
 
            'Header records
            sw.Write(gsRunMode)
            sw.Write(Environment.NewLine) ' end of line
            sw.Write(gsMemoText)
            sw.Write(Environment.NewLine)
            sw.Write(gsStatementText)
            sw.Write(Environment.NewLine)
            sw.Write(Environment.NewLine) 'Spacer
        End If
 
        If OutputFileType = ".xml" Then
            fileName = fileName & gsPickUpFileExtn
            'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
            'xWriter.WriteStartDocument()
            'xWriter.WriteComment("Customer file parsed using script")
            'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
            'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
            xWriter = New XmlTextWriter(fileName, Nothing)
            xWriter.WriteStartDocument()
            xWriter.WriteComment("Customer file parsed using script")
            xWriter.WriteStartElement("x", "customer", "http://some.org/name")
            xWriter.WriteAttributeString("FileName", fileName)
        End If
 
 
    End Sub
 
 
 
    Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
 
 
        If OutputFileType = ".csv" Then
            Dim delim As String = ","
 
            sw.Write(Row.ProjectID.ToString + delim)
            sw.Write(Row.TransactionRefNum.ToString + delim)
            sw.Write(Row.CustomerNum.ToString + delim)
            sw.Write(Row.AccountNum.ToString + delim)
                         
            sw.Write(Environment.NewLine) ' end of line
            sw.Flush() 'send the stream to file
        End If
 
 
 
        If OutputFileType = ".xml" Then
            xWriter.WriteStartElement("CUSTOMER")
 
            xWriter.WriteStartElement("ProjectID")
            xWriter.WriteString(Row.ProjectID.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("TransactionRefNum")
            xWriter.WriteString(Row.TransactionRefNum.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("CustomerNum")
            xWriter.WriteString(Row.CustomerNum.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("AccountNum")
            xWriter.WriteString(Row.AccountNum.ToString)
            xWriter.WriteEndElement()
 
             
 
        End If
 
 
 
       
       
    End Sub
 
    Public Overrides Sub PostExecute()
 
       
 
        If OutputFileType = ".csv" Then
            'Create the trailer
            sw.Write(Environment.NewLine) ' blank line
            sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible
            sw.Write(Environment.NewLine)
            sw.Flush() 'send the stream to file
            'Close file
            sw.Close()
        End If
 
        If OutputFileType = ".xml" Then
            xWriter.WriteStartElement("RecordCount")
            xWriter.WriteString(Me.Variables.giSuccessCount.ToString)
            xWriter.WriteEndElement()
            xWriter.WriteEndElement()
 
            xWriter.WriteEndDocument()
            xWriter.Close()
        End If
 
    End Sub
 
 
End Class
 
 
 



 

View Replies !   View Related
Script Component Has Encountered An Exception In User Code - Object Is Not An ADODB.RecordSet Or An ADODB.Record
hi have written SSIS script and i am using script component to Row count  below my code what i have written. and i am getting error below i have mention...after code see the error
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Data.SqlClient;

using System.Data.OleDb;

 

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

IDTSConnectionManager100 connMgr;

OleDbConnection sqlConn = null;

OleDbDataReader sqlReader;



public override void AcquireConnections(object Transaction)

{

connMgr = this.Connections.MyConnection;

sqlConn = (OleDbConnection )connMgr.AcquireConnection(null);

//sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

}

public override void PreExecute()

{

base.PreExecute();

/*

Add your code here for preprocessing or remove if not needed

*/

OleDbCommand cmd = new OleDbCommand("SELECT CustomerID,TerritoryID,AccountNumber,CustomerType FROM Sales.Customer", sqlConn);



sqlReader = cmd.ExecuteReader();

}

public override void PostExecute()

{

base.PostExecute();

/*

Add your code here for postprocessing or remove if not needed

You can set read/write variables here, for example:

Variables.MyIntVar = 100

*/

}

public override void CreateNewOutputRows()

{

/*

Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".

For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".

*/

System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();

//SqlDataAdapter oLead = new SqlDataAdapter();

DataSet ds = new DataSet();



System.Data.DataTable dt = new System.Data.DataTable();

//DataRow row = new DataRow();

oLead.Fill(dt,this.Variables.ObjVariable);





foreach (DataRow row in dt.Rows)

{

{

Output0Buffer.AddRow();

Output0Buffer.CustomerID = (int)row["CustomerID"];

Output0Buffer.TerritoryID =(int)row["TerritoryID"];

Output0Buffer.AccountNumber = row["AccountNumber"].ToString();

Output0Buffer.CustomerType = row["CustomerType"].ToString();

}

}



}

}
the error
 Script component has encountered an exception in user code
Object is not an ADODB.RecordSet or an ADODB.Record.
Parameter name: adodb  
   at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String
   srcTable, Boolean multipleResults)
   at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)
   at ScriptMain.CreateNewOutputRows()
   at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs,   
Int32[] outputIDs, PipelineBuffer[] buffers)
 
thanks
kedarnath

View Replies !   View Related
[ActiveX Script Task] Error: Retrieving The File Name For A Component Failed With Error Code 0x001B6438.
This error occurs when the ActiveX task tries to execute: 

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.

Anybody know how to troubleshoot these errors?  I can't find anything on this error code.  The same script works in DTS. 

 

 

 

 

View Replies !   View Related
The Component Metadata For &&"component &&"DataReader Source&&" Could Not Be Upgraded To The Newer Version Of The Component.
Hi,

I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.

Package works from my computer. But when I execute it on the server as a SQL Agent job, I get







The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.  

I copied the mdb file to a folder on the server which my packages have no problem reading data from.

My packages run under the same domain account as defined in proxies.

Appreciate a help.

Gulden

 

 

View Replies !   View Related
The Component Metadata For &&"component &&"DataReader Source&&" (1113)&&" Could Not Be Upgraded To The Newer Version Of The Component.
Hello,
 
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
 
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. 
 
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow.  [[The version or pipeline version or both for the specified component is higher than the current version.  This package was probably created on a new version of DTS or the component than is installed on the current PC.]]     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
 
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. 

 
Please advice.
Thank you.




 
 

View Replies !   View Related
Error: The ProcessInput Method On Component &&"OLE DB Destination&&" (513) Failed With Error Code 0xC0202009
I'm trying to create a simple Data transfermation.  I have a flat file that came of a unix server.. it's 177 bytes wide.. thought it was 175, but when I created the flat file connector, I could see some extra characters on the end.

 

My output is going to be an excel spreadsheet, I only want two columns from the input.  I created an oledb jet 4.0 connection. and followed instructions from here :

http://aspalliance.com/889_Extracting_Data_from_a_Flat_File_with_SQL_Server_2005_Integration_Services

to create my datafow.

 

On my first attempt to dataflow, I ran into unicode errors and had to do this:

 

ran into a problem with unicode errors. went to the source for the flat file. for the output column  in question changed to Unicode string [DT_WSTR].

 

When I run , here are the errors I get:

[OLE DB Destination [513]] Error: An OLE DB error has occurred. Error code: 0x80040E09. [DTS.Pipeline]

 Error: The ProcessInput method on component "OLE DB Destination" (513) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.

[GanchoFileSource [1]] Information: The total number of data rows processed for file "\ammia01dev04D$JCPcpmgancho_venta_20070321.sal" is 19036.

[GanchoFileSource [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "GanchoFileSource" (1) returned error code 0xC0209017.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.


 

View Replies !   View Related
Use Of A SSIS Variable Of Type “Object� Inside Script Component And Task Component
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n'  variables  of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!

View Replies !   View Related
A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

View Replies !   View Related
Reference To Preceeding Component From Custom Dataflow Transformation Component
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.

I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.

Does anyone have any suggestions?

TIA . . . Ed

View Replies !   View Related
Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005.  Given the original code here:
Function Main()
            on error resume next
            dim cn, i, rs, sSQL
            Set cn = CreateObject("ADODB.Connection")
            cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
            set rs = CreateObject("ADODB.Recordset")
            set rs = DTSGlobalVariables("SQLstring").value
 
            for i = 1 to rs.RecordCount
              sSQL = rs.Fields(0).value
              cn.Execute sSQL, , 128             'adExecuteNoRecords option for faster execution
              rs.MoveNext
            Next
 
            Main = DTSTaskExecResult_Success
 
End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially.  Upon this code's success, move on to the next step.  (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here.  From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here.  Any ideas/help on how to rewrite this code would be greatly appreciated!

View Replies !   View Related
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Dear Friends,

 

 I am having 2 Tables.

        Table 1: AddressBook
                Fields --> User Name, Address, CountryCode 

 

       Table 2: Country
                Fields --> Country Code, Country Name


   Step 1 : I have created a Cube with these two tables using SSAS.

 

 Step 2 : I have created a report in SSRS showing Address list.

               The Column in the report are User Name, Address, Country Name

 

               But I have no idea, how to convert this Country Code to Country name.

               I am generating the report using the Layout tab. ( Data | Layout | Preview )  Report1.rdl [Design]

 

              Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.

 


                  Thanks in advance.

 

 

Regards
Ramakrishnan
Singapore
28 March 2007 

View Replies !   View Related
Resource To Learn Code And Share Code And Knowledge
Learn, Read and Post C#, ASP.NET, JAVA, C/C++, PHP, LINUX, UNIX,WINDOWS VISTA Articles here and many more.... www.exposecode.com

View Replies !   View Related
Many Lines Of Code In Stored Procedure && Code Behind
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you

View Replies !   View Related
Custom Code (Embedded Code) Question
 

Hi all,
 
Could someone tell me if custom code function can capture the event caused by a user?  For example, onclick event on the rendered report?
 
Also, can custom code function alter the parameters of the report, or refresh the report?

Thanks.
 

View Replies !   View Related
Putting SqlDataSource Code In Code-behind
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks  geniuses.  

View Replies !   View Related
Enable Error Handling When Writing Custom Source Component /custom Error Handling Component.
1) We are  writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,

2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.

Thanks in advance.

 

View Replies !   View Related
&&<Code&&>-8462&&</Code&&>
Hi:

My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.

After I receive the message successfully...in the end I get this message sent...

<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">

<Code>-8462</Code>

<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>

</Error>

Why am i gettting this error after the conversation.

Thanks,

Pramod

View Replies !   View Related
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.
Hi All,
 
Recently in an SSIS package I am getting the following error for a particular Data flow task.
 




Error: 2008-01-25 12:01:48.58

Code: 0xC0202009

Source: Import Datasynapse Data User Events Source [3017]

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.

End Error

Error: 2008-01-25 12:01:48.73

Code: 0xC004701A

Source: Import Datasynapse Data DTS.Pipeline

Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.

End Error
 
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
 
Since this is very urgent, immediate response would be very much appreciated.
 
Thanks & Regards,
Prakash Srinivasan

View Replies !   View Related
How To Get The Name Of The Component
How can we get the name of the component inside the Data Flow Task . What I want is to log error stating which component in the data flow task has failed. Package and Data Flow names I am getting from system variables. I want to log like the Execution Result screen with Name of the component and [its id].
Like "Derived Column[216]" has failed with some error
It is possible?

View Replies !   View Related
Looking For A Component
I'd like to incorporate the "package component tree" UI component that is used within the Visual Studio designers into an SSIS utility I'm building. This is the one I'm talking about:
 
Edit: Apparently using the IMG tag on these forums works in the editor preview, but not in the actual posts, so I've replaced images with links...
 
Example 1
Example 2

 
I've done some searching online, but have not found any information about where this UI is implemented, or if it is reusable. Does anyone here know if it is possible to re-use this component in a .NET application?
 
Thanks in advance!

View Replies !   View Related
DTSHelper Component
A long long time ago.... (in a galaxy far away)....

There was some DTS Packages written with ActiveX Script Tasks included.... these used a component called DTSHelper....

Someone is looking at these packages and planning on doing some changes but needs the DTS Helper component and for the life of me I can't remember where it can from. Does anyone know where I can find this??

Cheers,
Shaun

View Replies !   View Related
What Component Do I Need To Use MSDataShape?
Hi, all.
I create a report using VB datareport.
but, when I call following statement.. it cause Error "Class not registered!"

cnn.Open "Provider=MSDataShape.1;Persist Security Info=False;User ID=" & gstrID & ";Password= " & gstrPassword & ";Initial Catalog=TimeClock;Data Provider=SQLOLEDB.1" & _
"Data Source=" & gstrLocalDBServer & ";server=" & gstrLocalDBServer

How can I solve this problem?

View Replies !   View Related
Installation Of Component
Ok. I have 2005 standard with sp2 installed. Went to create a maintenance plan and low and behold the component is missing. Ok.. no big deal, i'll just add it. Of course you know this is the error I get.

To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.

Ok.. doesn't seem too bad. So I go to a dos prompt. d: oolssetup.exe skuupgrade=1

Same error.

Help please :)

View Replies !   View Related
Script Component
Hi,
While using 'Script Component' in SSIS, how to use a user-defined variable created at the package level in the script?

View Replies !   View Related
(re)using Serviced Component In SQL CLR
Hello guys,
I am desperately trying to get some code running for reusing an existing EnterpriseService object (server activation) from SQL 2005.

I tried the sp_oa* approach ... it fails finally when calling methods or properties with "The RPC Server is unavailable".

After that I switched over to using SQL CLR procedures with the same success level ;).

Approach 1:
I do have the ProgID / ClassID. When I run Type.GetTypeFromProgID then I get back an object "System.___ComObject" ... Unfortunately I cannot (explicit) cast it to a particular interface (will result in a NULL exception) nor can I apply reflection (GetConstructor, GetMethod).

Approach 2:
Use Activator.CreateInstance(...) and alike. The creation fails with "LoadModule(), LoadFrom() .. is disabled in the host". Comparing that error message to the serialization assembly required for making webservice calls I thought I could create such a serialization assembly with Sgen and then it would work. But no way. The same error. :((

I know I could port the whole serviced component (actually .net 1.1) to .net 2.0; get rid of the enterprise services stuff and host the code in SQL 2005 directly. But I have other clients for this component and I do not want to re-invent the wheel. And porting the component would mean to use System.Messaging from inside SQL 2005 what obviously does not seem to be supported.

Is anyone of you guys seeing a chance? I am running out of ideas how I could make it work. :(
Any ideas? Other alternatives?

Thanks a lot, Frank

View Replies !   View Related
Script Component
i have 2 source columns testsource and testsource1 and 2 output columns

test and test2

 

i am passing them through a script component to  check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column

 

Is it that the script component can only look at one column?

 

this is the code i wrote

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

If IsNumeric(Row.testsource) Then

Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)

Else

If Row.testsource = "N.A" Then

Row.test = "NULL"

End If

Row.test = "NULL"

End If

 

 

If IsNumeric(Row.testsource1) Then

Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)

Else

If Row.testsource1 = "N.A" Then

Row.test2 = "NULL"

End If

Row.test2 = "NULL"

End If

End Sub

End Class

 

 

example of run

INPUT

  ,20070930
  ,20080331
  ,20070930
  ,20071130
  ,20070930
 ,
  ,
 ,
20070504,20070503
20080331,
  ,20070930
N.A. ,N.A.
N.A. ,N.A.

 

 

 

OUTPUT

 

NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.

 

 

as you can see the first column gets evaluated based on logic but the second column just gets passed through..

 

Thanks for any help in advance

 

smathew

 

 

View Replies !   View Related
OLE DB Destination Component
when loading the transformed data into OLE DB destination, there is no options to truncate destination table first. Have to insert a middle step to run script to truncate the destination table first.

I'm very confused. We even has the options of keeping or deleting the data in destination table in SQL2000 DTS package. Why we don't have this option in SQL2005??

 

View Replies !   View Related
Aggegrate Component
Hi,

i have 2.5million records which i have to aggregate on a couple of columns, then add those to another table.

i have created ole-db-source -> aggegrate

is this wise?

or should i use ole-db-source with a sql-query that aggregates for me...

will this increase my performance?

 

View Replies !   View Related
Union All Component
 

I am using a Union All component in a Data Flow Task. I have 3 Input OLE DB Sources connecting to the 1 Union All component. And then the Union All component connects to OLE DB Destination component. Everything looks good, but for some reason, for every time the Data Flow Task runs, 3 rows are inserted into my table. Why is that? Isnt Union All joining all fields, and then just inserting 1 final row to my table. What am I doing wrong or what should i do to just insert 1 row in my table. Thanks and I am really anxious to see responses on this post. Thanks again.

View Replies !   View Related
SCD Component - Is There An Alternative?
Hi,

I have several SCD components in my project. As I have to process millions of records, SCD's are taking a lot of time. Is there a way to speed them up? Work arounds?

Any tip is welcome

-Tom

View Replies !   View Related
Can Not Debug Component?
Who can help me?

I defined project properties:

Start action--start external program : dtexec

Action options--command line arguments: /f  ***.dtsx (using component which i debug)

Configuration: active(debug)

I specified breakpoint

after clicking build,dtexec can popup but then generate error:

The following modle was built either with optimizations enabled  or without debug information

C:winntassemblyGAC_MSIL******.dll

To debug this module,change it's project configuration to debug mode.

I have no idea about this,and i can find my dll file in C:winntassembly,but no GAC_MSIL folder

Who can help me? Thanks in advance

View Replies !   View Related
Script Component?
My issue: assumed table

id area block basement direction map remarks....more than 90 fields

1  002  Null   Y                northern  Null  aaa...

....

I need to put all the fieldname and fieldvalue into another table via id like shown below

id  fieldname   fieldvalue

1   area            002

1   block           Null

1   basement   Y

....

and continue,i still have to handle all the data,such as convert fieldvalue's data type and so on

Because my original data get from other component,i cannot use source script component

Any ideas? Regards

 

View Replies !   View Related
Component Caching
In Data Flow Task when you add Merge join with two data sets the association/mapping that you do the first time remains even after you remove the datasets that are feeding into the merge join and add new ones.  

The only way to remove the references is to remove the Merge Join component from the DFT and add a new one. Once you associate a set of inputs it always brings up the Restore Invalid Column References Editor.

Merge Join is an example but this is true for all task. How do I am make these components not cache the data?

Also is there a way to manually delete the output columns and external columns for such transformations that do not have adavanced editor option?

 

View Replies !   View Related
Retry Component
Hi,

I am trying to do a retry component.  My idea is to create a custom code that that should be place on the event handler - on error of every task.  The code should be able to identify which task failed, then retry X number of times after Y time interval based on package variables.  Do you think this is possible?  How? 

View Replies !   View Related
IS Component Upgrades
Has MS provided any updated IS components, like the Pivot component?  Where can updated components be found?

View Replies !   View Related
Component GUID
Exists any easy way to renew component GUID without having to recreate the component?
 
 

View Replies !   View Related
Script Component
 

I would like a derived column doing the following
 
if my column = 'something' and column2 in ('xx1', 'xx2', 'xx3')
then ....
 
 
how script component works to do this?

View Replies !   View Related
Script Component Help
 

I'm having some trouble getting a script component to work in one of my data flows.  I'm loading data from a flat file and essentially, all I want to do is delete any data in the table that has the same period and fiscal year as the data in the flatfile.  I was doing this with an OLE DB Command, and it worked, but very slowly.  So I thought I'd try using a  Script component to call a stored proc to handle things for me.  The problem is, while the script component calls the proc and deletes everything exactly as it should (I've checked the counts in the table before and after), it then just sits there forever, and the package never completes.  I imagine I must be doing something wrong with the script, since I don't really use VB all that much, but I can't seem to figure out where.  Here's what I'm using
 



Code Snippet
Public Class ScriptMain
    Inherits UserComponent
    Dim strFY As String
    Dim strPer As String

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        With Row
            strPer = Row.DataConversionPeriod.ToString()
            strFY = Row.FiscalYear.ToString()
        End With
        Dim strConn As String = "data source=server; initial catalog=database; integrated security=sspi;"
        Dim sqlConn As New SqlConnection(strConn)
        sqlConn.Open()
        Dim cm As SqlCommand = New SqlCommand("FinancialHE_DeleteExisting", sqlConn)
        cm.CommandType = CommandType.StoredProcedure
        cm.Parameters.Add("@FiscalYear", Integer.Parse(strFY))
        cm.Parameters.Add("@Period", Integer.Parse(strPer))
        cm.ExecuteNonQuery()
 
        cm.Dispose()
        sqlConn.Close()
        sqlConn.Dispose()
 

    End Sub
End Class
 
 


 

View Replies !   View Related
Script Component
 

I have a text file which is fixed length. I have a header, trailer and detail records. I need to validate thel records and load only the detail records to the SQL server database. So now in the data flow, I have input file transformation(1 column called "Line"), then i have the script component(parses the column "Line" - identifies header,detail and trailer and do validations. Then I have conditional split, which splits the detail records and the output transformation is SQL server.
 
My question here is I am basically a database developer, so coding in vb is new to me. So in the script component, how do i split the columns
 
something like this:
 

If Row.Line.Substring(1, 1).ToString().Trim() = "H" Then
Row.RecType = "H"
 

If Row.Line.Substring(15, 2).ToUpper().ToString().Trim() <> "" Then
Row.OutTranCode = Row.Line.Substring(15, 2).ToUpper().ToString().Trim()

End If
etc
etc.

 
.
elseif  Row.Line.Substring(1, 1).ToString().Trim() = "D" Then
Row.RecType = "D"
 
else
 
endif
 
 
Is that how it works or do you have some better idea to acieve this. Hope I am clear.
 
Thanks for your time and help
Kayal
 
 
 

View Replies !   View Related
Bug In XML Source Component??
Hi,
 
I have an XML data file and an associated XSD file with properly defined datatypes. However, the datatype of all the data elements are always "string" datatype. For example, in my current xml file, all the data elements are of Decimal datatype which is properly defined in XSD file. However, datatype of all the output columns are of string datatype.
 
Is it a bug or am I doing something wrong?
 
Thanks
Navnish

View Replies !   View Related
If-then-else-component In SSIS
Hi all,

i am not very experienced with the SSIS. I am just wondering if there is something like a "if-then-else"-componente like the foreach-component in SSIS.

I want to delete the values of all tables in one database. So I took a foreach-component and selected the smo-enumeration with all tables. I store the tablename in a variable and execute a sql-task with "delete table.." with the variable tablename as parameter. Now I want to delete all except one certain table. I would like to add a selection where the variable tablename is checked. If the tablename is this certain table, I don't want to execute the sql-command, else I want to excecute the delete-command.

Are there any suggestions?

Thank you very much

Joachim

View Replies !   View Related
Scripting Component.
Hi Guys,
 
I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.
I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.
Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.
Any quick help would be really appreciated.

TA
Gemma

View Replies !   View Related
Component Icons
 

Hi,
I'm trying to add an icon to a custom component but its not happening and I can't figure out why. I'm sure I've managed to do this in the past. Here's my DtsPipelineComponent attribute:
 



Code Snippetnamespace Jamiet.Ssis
{
    [DtsPipelineComponent(
        DisplayName="MyComponent",
        Description="Does some stuff",
        ComponentType=ComponentType.Transform,
        NoEditor=true,
        IconResource="MyComponent.ico"
        )
 
 


MyComponent.ico is stored in the same folder as my project (i.e. at the same level as the .cs file and the C# project file).
In the application property page I've pointed the icon resource at MyComponent.ico
 
Everything compiles OK. But I see no icon in my component when I deploy it to the toolbox or use it in a package.
 
I've also tried changing the attribute to this:
 



Code Snippetnamespace Jamiet.Ssis
{
    [DtsPipelineComponent(
        DisplayName="MyComponent",
        Description="Does some stuff",
        ComponentType=ComponentType.Transform,
        NoEditor=true,
        IconResource="Jamiet.Ssis.MyComponent.ico"
        )
 
 

Again, it compiles OK, but no icon!
 
Any ideas what I'm doing wrong?
 
Thanks
Jamie
 
 
 

View Replies !   View Related
ReportViewer Component
Hi all,

I'm creating a custom interface for reporting services but I am having a few problems.  This is my environment:

Using the ReportViewer component in remote mode to show my reports of the report server.  There are 2 user levels of which any user can be apart of, Admin & user.  In every report, there is a parameter called MERCHANTID, when the user who logs in (via custom login interface) is of group admin, then the merchantid parameter gets prompted before the report is run, if the group of the user is "user" then the merchantid parameter is hidden, and is passed programatically to the report.  The report path also

 

My Questions:

1.  When my reportviewer component loads up the report and prompts for a parameter, when you select a parameter value or type it in, it just posts back with no results, just the parameter prompt again with no data... How can this be corrected?

 

2.  How do I pass report parameters programatically... so that when my user id of group "user" then "merchantid" gets passed automatically, but if there is any more parameters, then that gets prompted...

 

Thanks for your help.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved