Case conversion with SQL or Stored Proc

Hi experts,

I m new in SQL stuff. I have to work out a fucntion with ASP.net for CSV import to the DB in MSSQL.

I would like to know for Stored Proc, is there any way that I can do the case conversion?

e.g

the Full_Name read from CSV file: Lennon, John

Then for the family name I need to convert into uppercase so the converted one: LENNON, John

Is there any way I can check those words before the comma? The CSV file is delimited with | instead of , ?

Can I use substring for that? And also do you have any online tutorial for Stored Procedure recommended? Thanks a lot!!!

Cheers,

KNVB

Hi,

Here is an example:
declare @.fullName varchar(100)

set @.fullName = 'Lennon, John'

select upper(left(@.fullName, charindex(',', @.fullName) - 1)) + right(@.fullName, len(@.fullName) - charindex(',', @.fullName) + 1)

Note: The first two lines of code are just for the sample

It might be better to implement this as a FUNCTION in case of a STORED PROCEDURE since functions can be used in your select statement.

References:

Creating stored procedures: http://www.sql-server-performance.com/tn_stored_procedures.asp|||

Merci beaucoup, Geert!

By the way, have you heard of a company called i4net from Namur?

|||

No problem, glad to help.

I didn’t know i4net. Is this your company maybe?

Greetz,

Geert

Capturing rows inserted from bulk insert

Is there any way to capture the count of rows processed by a bulk insert task?
A property perhaps?
I can run queries post import but would prefer it if there was a way to capture that number directly. It was something we had in the old DTS as the package ran. Anything we can do to discover it in SSIS?

Paul PisarekOne option is to enable the out-of-box logging to sysdtslog90 table - the components displays number of rows processed. It's in the 'message' text colum though, so you need to add a little bit of parsing to get your specific data.
KDog|||That's right. We're working on a sample that parses the string and creates a report from it, hopefully should be available to you soon. But, yes, parsing the log is the right approach.

Can't you have a variable TOP in a select statement?

Hi,
I got a stored procedure like this
CREATE PROCEDURE dbo.readImport
(
@.Start INTEGER,
@.Number INTEGER
)
AS
SELECT TOP @.Number * FROM Import WHERE RowID >= @.Start ORDER BY RowID
GO
However, it doesn't seem to like having an unknown @.Number.
Any ideas?
MortenHi Morten,
If you are using SQL 2k its not possible.
The only thing is to use dynmiac sql for that.
HTH, Jens Suessmeyer.|||Ok, thanks
Morten
On Fri, 11 Nov 2005 09:30:25 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>|||... or SET @.@.ROWCOUNT...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1131697825.522144.101160@.g49g2000cwa.googlegroups.com...
> Hi Morten,
> If you are using SQL 2k its not possible.
> The only thing is to use dynmiac sql for that.
> HTH, Jens Suessmeyer.
>

Can't use Package Variable to specify the filename for a Connection

I'm attempting to modify a flatfile import package so that I can reuse it to import several other files into the same table. I was unable to use package variables (containing the import filename) in the Flat File Connection Manager file name field. What is the proper way to solve this kind of problem in SSIS?Have you looked up property expressions in BOL? Give that a look.
http://sqljunkies.com/WebLog/knight_reign/archive/2005/02/12/7750.aspx
Thanks,
K|||

I setup a ForEach Loop Container to do this.

Create a Package Variable, for example, "varFileName", for the FileName. Then edit the ForEach Loop container. Select "Variable Mappings". Select the drop-down under the "Variable" column and select your "User: varFileName" variable. The index column should read "0". Save the object. Now add a Data Flow task and double-click to edit.

In the Data Flow window, create a Source Script transformation. Define your output columns on the "Inputs and Outputs" option. Click "Script" and add your variable "varFileName" to the property "ReadOnlyVariables". Now click the "Design Script" button, which opens up another window for scripting.

Add "Imports System.IO" to the top so you can read the flat file.

Read the file with the following code:

Dim strRecord as String
Dim objFlatFile As StreamReader = File.OpenText(Variables.varFileName)
Do Until objFlatFile.Peek = -1
strRecord = objFlatFile.ReadLine
[OutputBuffer].AddRow()
[OutputBuffer].[Column1] = Mid$(stRecord,1,4)
... etc.
Loop

Let me know if this helps...