I don't know about SSIS, but certainly you can read the file using OPENROWSET BULK

SELECT col1, col2 FROM OPENROWSET (BULK 'C:\sample.txt', FORMATFILE = 'C:\sample.fmt') AS Z

sample.fmt

7.0

3

1SQLCHAR00"\"" ...

Paul White NZ (9/2/2010)

---

Ok, let's go with that. Thanks for persisting...

Yes precisely, the number of rows returned from the split is...

Paul White NZ (9/2/2010)

---

Not sure I agree - a single trailing delimiter shouldn't return an extra...

Great stuff Paul:-)

Just one minor problem that it doesn't return the...

Paul White NZ (9/1/2010)

---

Well the...

I think it just needs 3 quotes either side of @db_name

select @SQLString ='select ''' + @db_name + ''' as databse,type_desc,create_date,modify_date from [' + @db_name + '].sys.procedures

where create_date > @create_date

order by...

Paul White NZ (9/1/2010)

---

On my machine, the fastest solution is based on Adam Machanic's excellent CLR string splitter. I include it here for others to compare:

CREATE ASSEMBLY Utility

...

Does this help you?

SELECT BulkColumn FROM OPENROWSET (BULK 'C:\test.txt', SINGLE_CLOB) AS Z

You can use CROSS APPLY if you need to retrieve EditedDate in your results set

IF NOT OBJECT_ID('tempdb.dbo.#Files', 'U') IS NULL DROP TABLE #Files

IF NOT OBJECT_ID('tempdb.dbo.#FileContent', 'U') IS NULL DROP TABLE...

Can't you just add up the columns then?

SELECT

Product,

col1,

col1 + col2,

col1 + col2 + col3,

col1 + col2 + col3 + col4

FROM YourTransformedTable

Try

SET QUOTED_IDENTIFIER OFF

A variation which performs the split on the fly

WITH cte1 AS

(

SELECT '1,2,3,5,6,8,10,50' AS InputString

)

,

cte2 AS

(

SELECT CONVERT(INT, Value) AS Value FROM cte1

CROSS APPLY

(

...

Lowell (8/26/2010)

---

ok, I just realized that the shortcut command you needed was for {slash L}, not {slash R]

ROWTERMINATOR =

vbCrLf = CHAR(13) + CHAR(10) = \n

vbCr = CHAR(13) = \r

vbLf...

You will need to put square brackets around your index and table names