Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

exec sp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file = 'c:\interface files\CostCentreImportFile.txt'

SET @.SQL = 'SELECT * FROM OPENROWSET(BULK N''' + @.file + ''',single_clob) AS doc'

PRINT @.SQL

EXEC sp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROM OPENROWSET(BULK @.filename, SINGLE_CLOB) AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql = 'OPENROWSET(BULK ' + @.filename + ', SINGLE_CLOB)'

SELECT @.strXML = x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.