.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS Function for find the Occurance of a character

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

Please help me to find the solution for the below requirement.

Input data: "AAAA,BBBB,CC" or "A,B,CCC"

I need to check whether the record is having any comma(,) or not. I need to check this this in a "conditional split transformation".

Please let me know which function will help me.

Many Thanks,


View Complete Post

More Related Resource Links

Program to find first non repetable character in a string without using any buitin function?


Program to find first non repetable character in a string without using any buitin function?

Need function to find difference between two dates


Hi all

I need a function written in vb to find the exact month difference between two dates.

For example

    Start Date : 01/01/2010

    End Date   : 15/02/2010

 then the difference value should be like this 1.5  i.e i need the difference in decimal value.

Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

How to create a "search function" to find files and folders using C++ windows forms?

As mentioned above, how can I make a windows forms application that searches for files and folders specified by the user and display results in a ListBox? Thanks in advance another question: How can I make a textbox transparent? I would be really grateful if anybody told me what books are good to learn windows forms using C++? thanks again

SSIS And Sybase Source OLEDB connectivity issue with code page for character set

Hi I am using SSIS 2008 to connect the Sybase Server version 15.0.3 64-bit. The default character set id used by this Sybase server is 4 (i.e. roman8). (http://manuals.sybase.com/onlinebooks/group-charc/chg0300e/charsets/@Generic__BookTextView/1706;pt=266) I had installed Sybase Client for Sybase OLEDB Driver (ASEOLEDB) to integrate this in OLEDB Source Editor. As per guidance from one of the past MSDN forum discussion, I tried setting property of AlwaysUseDefaultCodePage property to "FALSE"  (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5af9328-e1fb-48d7-a85d-1d08bb7cf0e5/). However, I am still experiencing error message as follows:- ********************************************************* Error at Package2 [Connection manager "xxxxxx.xxxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[00000] [Native Code: 30061] [ASEOLEDB]Could not load code page for requested charset". An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[1ZZ001] [Native Code: 30016] [ASEOLEDB]Internal Error". Error at Data Flow Task 2 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMC

How to read Hex 00 character in SSIS

hello there   i have a large text file which i need to import into sql server. i have been able to map the fields and load the data. it is a fixed width file. the process works. however during testing i found that there are some character which are not visible in notepad. on a hex editor they show the value of 00 - null. in the file . the ssis package reads till the column it finds null and then considers it as a carriage return and moves to the next line.   please suggest how i can resolve this issue. i wrote a vbscript where i could open the file and save it again using notepad, it works for small files. but my input file is around 2 gb.   thanks prasanna

SSIS throws error when it doesn't find file

Hello All, I am facing one issue that i dont know in advance if there is file on FTP or not. I scheduled a job which runs every hour which in turn calling SSIS package. Now, when there is file on FTP, it works fine. But, when there is no file on FTP then itthrows error which i want to handle. Is it possible to check for any files present on FTP? Cheers, Pranav

Wild card filter character in SSIS

In my Data flow task i need to select certain rows which are matching my criteria. The criteria requires using wild card characters like Field1 = '%aaaa%'  or Field1 = '%bbbb%' ......                                                                          Field2 = '%aaaa%'  or Field2 = '%bbbb%' ...... There are 6 fields ( Field1.. Field6 ) that have to be compared against values from a table ( aaaa,bbbb,ccccc etc from table) FindString is not a option. Any help will be appriciated!  

trying to use custom assembly with script task in SSIS 2008 - can't find correct version of GACUtil


I am trying to use a custom assembly (compiled with VS 2005 framework 2.0) with SSIS 2008.  I understand that I need to put this Assembly in the GAC.  I initially attempted to do this using the GACUtil, but I apparently only have version 1.1 of GACUtil intalled on my development PC.  Shouldn't version 2.0 have been installed as part of VS 2005? 

So I then tried simply copying the dll into the GAC folder using windows explorer.  Well, the dll is now visibly in the GAC folder, but I cannot see it from either VS 2005 or BIDS 2008.  Does that mean I do need to use GACUtil 2.0 or higher to install the DLL into the GAC?  If so, is there any place on the Web that I can download it?

How do I find which queries use a given function



I have a Table-valued Function and a large number of views that potentially use it, I would like to produce a list of views in which the function is called as I need to edit each to make changes.

I am using SQL Server 2005, I have used the right-click to look at dependencies and this brings up a short list of SPs in which the function is used. I know there are views that call the function.

I hope this makes sense.

Find the position of a character



we are working with sql server 2005. My xml comes like this:

<ImportResponse xmlns="aaa:import:response:1.0" timestamp="2010-09-24T15:38:40">
<Summary failures="2" successes="3">
 <Message code="-304" level="error">One or more errors have occurred while processing the
  request items
 <Result id="50063,123" success="false">
    <Message code="-1100" level="error">Invalid field format (knect) - The Value fails meets the
     requirements for this field: MaximumLength=9, Precision=0 - {3}
 <Result id="50064,124" success="false">
    <Message code="-1100" level="error">Invalid field format (knect) - The Value fails meets the requirements for this
  field: MaximumLength=9, Precision=0 - {3}


I need to remove the value after ',' from Result element ID attribute. I need to do this

SSIS cannot find excel columns when using SQL command to import

I'm trying to import an Excel file, but receive the following error during the process:

Validation is completed
[DTS.Pipeline] Information: Prepare for Execute phase is beginning.
Progress: Prepare for Execute - 0 percent complete
Progress: Prepare for Execute - 33 percent complete
Progress: Prepare for Execute - 66 percent complete
Progress: Prepare for Execute - 100 percent complete
[DTS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
[Source - Individuals$ [1]] Error: Column "Last Name 2" cannot be found at the datasource.
[DTS.Pipeline] Error: component "Source - Individuals$" (1) failed the pre-execute phase and returned error code 0xC0202005.

My Excel Source is using "SQL command" data access mode:
Select * from [Members$]

Is there something that I'm missing when using the 'SQL command' mode? Thanks in advance for any help.

Calling scalar valued Function from SSIS OleDB Command Transformation


Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!! 

Writing a custom Aggregate function in SSIS


Hi is it possible to write a custom aggregate function in SSIS. Is there any other way to do it better? Any help is highly appreciated.


My values are like this

Item | VALUE








I am trying to bring the output. 


1, abc-xyz-wer

2, def-tyu

3, ppl

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Running SSIS job step as Proxy account does not find log provider for windows event log

When I run a ssis job step using a proxy account i get this error message:

Description: The log provider type "DTS.LogProviderEventLog.2" specified for log provider "SSIS log provider for Windows Event Log" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name.  End Error  Error: 2010-02-02 14:43:03.88     Code: 0xC0010021     Source:       Description: Element "{028F8760-7E28-4F62-A204-66F33F4064BE}" does not exist in collection "LogProviders". 

If the account is a member of administrators group on the sql server the job step works fine.

Since the logprovider DTS.LogProviderEventLog.2 exists on the system I guess that this has something to do with access rights, probably to some registry key(s), but I cannot find out which part of the registry the proxy user does not have access to.

When running the same package using DTEXEC in a Command Prompt and using RUNAS <proxyuser> the package works fine and writes to the event log.

Kind regards


update 17-Feb-2010: Microsoft has reproduced this situation and I hope to get a workaround or fix soon :-

Can't find "Analysis Management Objects" in SSIS Script Task reference



I am trying to connect to an Analysis services cube through my script task. To achieve this, i will have to use the Microsoft.AnalysisServices namespace. For this, I need to add a reference in my Project explorer space, to "Analysis management Objects" . However, I am not able to find this. Does any one know where I can find the dll file required for this, or why it's not showing in the 'Add Reference' of my script task?



--Mark the thread as answered if one of the replies answers your question. Thank you

Cannot find either Column "dbo" or the user-defined function or aggregate "dbo.GetCandiateID", or th



Good morning to everybody. I am not understanding why I am getting the following error (also mentioned in the subject line).

I have written the following function in SQL Server 2005:

    declare @candidate_id int
    SELECT @candidate_id = max(c.candidate_id) from dbo.candidates c
    if(@candidate_id is null)
        set @candidate_id = 1001
        set @candidate_id = @candidate_id + 1
    return @candidate_id

The function compiled properly. I have used the above function like below in the query:

select dbo.GetCandidateID()

I am getting the following error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetCandidateID", or the name is ambiguous

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend