Solved

charindex()

Posted on 2009-04-03
5
1,481 Views
Last Modified: 2013-11-10
Hi,
i am trying to find out first part of my filename is numeric or not using followed function
DECLARE  @filename varchar(100)
SET @filename = ?
SELECT     isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1
when i give the filename directly and check it works well in SSMS,but when i pass the file name through an input param in SSIS it fails..any help appriciated..
0
Comment
Question by:ametuer999
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24067234
Is the entire thing throwing an error?  I'm not familiar with passing parameters to SSIS, but perhaps the ? has to be in quotes.  I thought that you could read the variable another way though.


DECLARE  @filename varchar(100)
SET @filename = '?'
SELECT     isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24068581
For testing purposes add a "print @filename" statement to see what the variable is actually is.

Where is the question mark coming from anyway?
DECLARE  @filename varchar(100)
SET @filename = ?
print @filename
SELECT  isnumeric(substring(@filename, 1,(charindex('_', @filename,1)-1))) AS result1

Open in new window

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24073473
You could also check this using script rather than T-SQL. Then you don't need a database connection to check if your file is numeric.
0
 

Author Comment

by:ametuer999
ID: 24073726
could you please give me a sample code...like....
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 24074426
Below is some very simple code.
It inspects a variable called FileName. It grabs the characters before an underscore and checks if they are numeric.
It uses dialog boxes to help you debug.
To use it:
1. Drag a Script Task on to your page
2. Double click it. Press the Script selector on the left. Press the Design Script button
3. Paste in the code below
4. Ensure you have a variable called FileName, with a value in it
5. Right click on the script task and press Execute Task
You'll see some message boxes come up and finally tell you whether the chars preceding the _ are numeric or not.
If this suits you let me know and we can integrate it further. I assume this relates to your other question about looping through files.
PS I am not a .Net coding expert, there may be optimisations on this code.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class ScriptMain
 
    Public Sub Main()
        Dim sVariable As String
        Dim iInteger As Integer
        Dim bResult As Boolean
        Dim vars As Variables
 
        Dts.VariableDispenser.LockOneForRead("FileName", vars)
        sVariable = vars(0).Value.ToString
        vars.Unlock()
 
 
        MsgBox("Value of variable is [" & sVariable & "]")
 
        If sVariable.IndexOf("_") > 0 Then
            sVariable = Left(sVariable, sVariable.IndexOf("_"))
        Else
            sVariable = ""
        End If
 
        MsgBox("Value of string preceding _ is [" & sVariable & "]")
 
        bResult = iInteger.TryParse(sVariable, iInteger)
 
        MsgBox("Starts with numeric? " & bResult.ToString)
 
        Dts.TaskResult = Dts.Results.Success
    End Sub
 
 
End Class

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Merge Join and Conditional Split in SSIS 6 78
Error when creating an UPDATE Trigger in SQL 6 20
SQLCMD Syntax 2 13
job schedule 8 18
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question