Using VB Code In Script Component

Nov 30, 2006

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 1 Replies


ADVERTISEMENT

Serious Script Component Bug - Clears Out All Code Inside Component

Nov 27, 2007



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 4 Replies View Related

Code Sample For SSIS Script Component

May 15, 2007

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 2 Replies View Related

Script Component Can't Find The Binary Code

Jul 19, 2007

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 6 Replies View Related

Script Component - Code Not Saving After Edit

Mar 29, 2007

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 2 Replies View Related

Script Destination Component Code Sample Here That Creates A Csv Or Xml File

Jul 11, 2006

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 6 Replies View Related

The Product Level Is Insufficient For Component When Executing Package Using C# Code.

Jan 30, 2007

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 5 Replies View Related

Integration Services :: How To Get Content Of Cache Connection Manager In Script Component By Code

Jul 27, 2015

ConnectionManager manager = Microsoft.SqlServer.Dts.Runtime.DtsConvert.GetWrapper(base.Connections.Connection);
IDTSConnectionManagerCache100 cache = manager.InnerObject as IDTSConnectionManagerCache100;
if (cache != null)
{
  System.Windows.Forms.MessageBox.Show("Cache is found.");
}
and use
IDTSConnectionManagerCacheColumn100 id = connMgr.Columns["Id"]; get the column info.

but how do i get the cache connection content ?I want to look in the content in a script component code. 

View 4 Replies View Related

Integration Services :: SSIS 2012 Script Component Binary Code Not Found

Apr 21, 2015

After adding Service Reference  to WebService, the Script Component has Binary Code not found, red circle not showing these are the steps I followed:

1) Add Script Component as Source
2) Add 3 x Output Columns Col1,2 and 3
3) Add HTTP Connection URL>..Binary Code not found, red circle showing
4) Add test code to Sub CreateNewOutputRows     Dim i As Integer = 6       Binary Code not found, red circle not showing
5) Add Service Reference URL...Binary Code not found, red circle showing again

Should just adding Service Reference cause Binary Code not found, red circle to appear. I have to set  precompilescripttobinary option , however cannot see this option in 2012

View 3 Replies View Related

The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.

Oct 26, 2007

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 7 Replies View Related

The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.

Jan 23, 2007

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 4 Replies View Related

Error: The ProcessInput Method On Component OLE DB Destination (513) Failed With Error Code 0xC0202009

Mar 22, 2007

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 4 Replies View Related

Script Component Has Encountered An Exception In User Code - Object Is Not An ADODB.RecordSet Or An ADODB.Record

Nov 26, 2007

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 4 Replies View Related

[ActiveX Script Task] Error: Retrieving The File Name For A Component Failed With Error Code 0x001B6438.

Jul 28, 2006

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 8 Replies View Related

Use Of A SSIS Variable Of Type “Object� Inside Script Component And Task Component

Mar 16, 2007

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 3 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

Aug 13, 2007

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 4 Replies View Related

Reference To Preceeding Component From Custom Dataflow Transformation Component

Mar 30, 2006

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 7 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

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 7 Replies View Related

How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)

Mar 28, 2007

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 4 Replies View Related

Many Lines Of Code In Stored Procedure && Code Behind

Feb 24, 2008

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 2 Replies View Related

Custom Code (Embedded Code) Question

Oct 16, 2007



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 2 Replies View Related

Putting SqlDataSource Code In Code-behind

Jan 25, 2007

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 2 Replies View Related

Enable Error Handling When Writing Custom Source Component /custom Error Handling Component.

Apr 21, 2006

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 1 Replies View Related

&&<Code&&>-8462&&</Code&&>

Apr 19, 2006

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 7 Replies View Related

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB Error Has Occurred. Error Code: 0x8000FFFF.

Jan 28, 2008

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 4 Replies View Related

How To Get The Name Of The Component

Nov 26, 2007

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 8 Replies View Related

Looking For A Component

Nov 8, 2007

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 5 Replies View Related

What Component Do I Need To Use MSDataShape?

Apr 15, 2004

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 1 Replies View Related

Installation Of Component

Nov 16, 2007

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 1 Replies View Related

Script Component

Dec 6, 2007

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

View 2 Replies View Related

Component Icons

Sep 5, 2007



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 5 Replies View Related

ReportViewer Component

Feb 17, 2006

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 11 Replies View Related

Script Component

May 31, 2007

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 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved