Dynamically creating SSIS package for each flat file

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.

Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.

What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

Trav2003 wrote:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.

Dynamic Textbox size

I want to create a TextBox with dynamic width.
Probably reading from an expression.
Has anyone tried to set the width of the report objects with an expression.I posted a response here:
http://www.reportingservicesfaq.com/ow.asp?DynamicTextboxSize
Help build the Wiki!
--
Reporting Services Articles, Forums, Blogs and Wiki Community
www.ReportingServicesFAQ.com
> I want to create a TextBox with dynamic width.
> Probably reading from an expression.
> Has anyone tried to set the width of the report objects with an expression.
>
>|||There is a CanGrow property on the textbox - however, it will allow the
textbox to grow vertically but not horizontally.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"newbie" <newbie@.discussions.microsoft.com> wrote in message
news:0A8894B2-A1C3-4B7E-B1FF-555D2C1A5866@.microsoft.com...
> I want to create a TextBox with dynamic width.
> Probably reading from an expression.
> Has anyone tried to set the width of the report objects with an
expression.
>
>

Dynamic table name in destination

How to create a new table dynamically in OLE DB destination.

This is what i am doing

I am reading multiple flat files in loop and saving file name to a variable. Then i have a source script component which read and transforms data .Now how can I push the data to SQL table. I want to create a new table with name saved in a variable. I tried using OLE DB destination and assigning table name from variable. Does'nt work.

Thanks in advance for any insight on how to make this work.

-Amar

I think you'll have to break this up into multiple work flows. The first executes an Execute SQL task in the control flow to create the table. Then attached to that Execute SQL Task is the data flow that operates on the file and then inserts into the destination, which is derived from the incoming table name.

That may work for you. The problem is, the OLEDB Destination cannot create a table at run time.|||

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

|||

Amar Khaira wrote:

Ok i broke into multiple work flows. Now i read file names and then execute a script task to create table(it worked). Then read and transform data..but how to attach destination to it or how to insert into SQL table.

Can you please throw me some example.

-Amar

In the OLE DB Destination, set it to "Table name or view name variable - fast load". Then just pick the variable that you loaded in the foreach loop. The metadata must be the same, though, for each table.|||

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

|||

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

|||

Amar Khaira wrote:

I am reading files in ForEachLoop container and creating tables(works fine) but how to tell the OLE DB destination about table because the table does not exist yet. It will be created during runtime. OLE DB Destination needs to map the columns...in order to insert..that's why it does not work...any workaround for that.

-Amar

You need to create one table first. Then populate the variable that you are using in the foreach loop with a default value of that table you just created. Then in the OLE DB destination, in selecting that variable, it will read it and find the table. You can then perform your mappings. When you execute the package, the default value of the variable will be over-written.|||

Amar Khaira wrote:

Phil,

I tried that I am getting "Object does not exist in database" which i understand that table will be created a runtime but not there yet. So OLE DB does not know what to map.

-Amar

See my comment above.|||

Phil,

That what I did...can you please elaborate more...what i am missing

|||

Amar Khaira wrote:

Phil,

That what I did...can you please elaborate more...what i am missing

You need to create a table first. Then, in the variable you are using, TYPE in the name of that table in the DEFAULT VALUE parameter of that variable.

Then in the OLE DB Destination, do as I said above. Select that variable, and it should work for you.|||And this next comment is important, so I'm going to make it its own post:

The metadata (number of columns, data types, etc...) must be the same across all of your tables that you are dynamically feeding into the OLE DB Destination.|||

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

|||

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

|||

Amar Khaira wrote:

Phil,

It works that way...if i already create the table before running the package. But if i remove the tables from the db and run the package again it fails.

But SSIS should create tables during runtime.

Try setting the "ValidateExternalMetaData" property on the OLE DB Destination to false. If that doesn't work, then set the "DelayValidation" property of the data flow to true.|||

Amar Khaira wrote:

Phil,

I got it...I just had to change "Validate External Metadata" property of OLE DB destination to False.

It works all fine now...thanks for your help;)

Excellent. Please mark one of these posts as the answer to your question.

Thanks,
Phil

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.

Dynamic SQL reading statements from table

Hi,

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

I get: Incorrect syntax near 'sp_executesql'.

If I run

sp_executesql 'SELECT foo FROM foostable'

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

sp_executesql N'SELECT foo FROM foostable'

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

This is SQL Server 2005 SP2. Thanks in advance.On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)
>
Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql
>
I get: Incorrect syntax near 'sp_executesql'.
>
If I run
>
sp_executesql 'SELECT foo FROM foostable'
>
I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run
>
sp_executesql N'SELECT foo FROM foostable'
>
it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.
>
This is SQL Server 2005 SP2. Thanks in advance.

Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.|||On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:

Quote:

Originally Posted by

On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

>

Quote:

Originally Posted by

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

>

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

>

Quote:

Originally Posted by

I get: Incorrect syntax near 'sp_executesql'.

>

Quote:

Originally Posted by

If I run

>

Quote:

Originally Posted by

sp_executesql 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

>

Quote:

Originally Posted by

sp_executesql N'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

>

Quote:

Originally Posted by

This is SQL Server 2005 SP2. Thanks in advance.

>
Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -
>
- Show quoted text -

as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.|||No, I have printed it, it's fine. No truncation. Like my example
above, I can't even get a simple short statement to work (my real
example is 42 characters), and I don't see the error.

On May 1, 5:13 pm, manstein <jkelly.ad...@.gmail.comwrote:

Quote:

Originally Posted by

On May 1, 5:08 pm, manstein <jkelly.ad...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

On May 1, 5:04 pm, downwitch <downwi...@.gmail.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm using a 3rd-party app's back end which stores SQL statements in a
table, so I have no choice but to use dynamic SQL to call them (unless
someone else knows a workaround...)

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Problem is, I can't get the statement to run properly, and I can't see
why. If I execute even a hard-coded variation like

>

Quote:

Originally Posted by

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
sp_executesql @.sql

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I get: Incorrect syntax near 'sp_executesql'.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

If I run

>

Quote:

Originally Posted by

Quote:

Originally Posted by

sp_executesql 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I get: Procedure expects parameter '@.statement' of type 'ntext/nchar/
nvarchar'.
which I understand, as it's omitting the N converter--so if I run

>

Quote:

Originally Posted by

Quote:

Originally Posted by

sp_executesql N'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Quote:

Originally Posted by

it's fine. I don't understand why the first version fails. Is it some
sort of implicit conversion downgrading @.sql? Every variation of CAST
and CONVERT I use has no effect.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

This is SQL Server 2005 SP2. Thanks in advance.

>

Quote:

Originally Posted by

Try printing your @.sql parameter and then firing it mannually, you
might find that the string is not what you expect. Anyway, that is my
standard way of debugging dynamic sql.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
as an addendum, what is your (MAX) size? If its too small to hold all
the characters in your string, your statement will be truncated and
raise an error.

|||You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.

Just try:

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
EXEC sp_executesql @.sql

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||OK, yes, that does solve the first problem, thank you. Now for
another, related. I'm using a variation on Erland's proc here
http://www.sommarskog.se/dynamic_sql.html#quotestring
to handle nested quotes. There are none, of course, in the simple
statement, but running it through the proc causes it to fail
nonetheless.

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----

So running

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

On May 1, 5:28 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You are missing EXEC... It is optional only when you execute stored
procedures that are the first statement in the batch.
>
Just try:
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
EXEC sp_executesql @.sql
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

|||downwitch (downwitch@.gmail.com) writes:

Quote:

Originally Posted by

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----
>
So running
>
DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql
>
I now get: Incorrect syntax near 'SELECT foo FROM foostable'
>
Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

I added a PRINT @.sql to your SQL batch, and this is what I saw:

'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.

A string on its own is not legal T-SQL.

I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.

Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||manstein (jkelly.admin@.gmail.com) writes:

Quote:

Originally Posted by

as an addendum, what is your (MAX) size?

MAX implies in SQL 2005 a size of two gigabytes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Read that section in Erland's article again. The use of this function is
wrap an input parameter in quotes. I do not see any parameters in your SQL
statement, so no need to use the function.

Plamen Ratchev
http://www.SQLStudio.com|||On May 1, 6:14 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

manstein(jkelly.ad...@.gmail.com) writes:

Quote:

Originally Posted by

as an addendum, what is your (MAX) size?

>
MAX implies in SQL 2005 a size of two gigabytes.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.|||Perhaps I wasn't as clear as I should have been. Regardless, the
problem is solved--turns out it wasn't just "related" to the first
problem, it *was* the first problem. That's why I'd introduced the
quotestring function, actually, because when I switched from variable
to SQL (on a more complicated query, obviously, than the example I
provided, including multiple parameter values), the string failed
without doubling its parameter quotes. And then I was seeing the
quotes around the SQL string as an output result, not a part of the
string...

In short, duh on me.

RE the security risk, I'm fully aware of it. But as is often the case
with a very sensitive db, if anyone even gains access to it in the
first place there are much bigger potential headaches than whether or
not they want to drop a nasty dynamic SQL statement on it.

On May 1, 6:13 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

downwitch (downwi...@.gmail.com) writes:

Quote:

Originally Posted by

Here's my version of the function:
----
CREATE FUNCTION uQuoteString(@.str nvarchar(MAX)) RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @.ret nvarchar(MAX),
@.sq nvarchar(4)
SELECT @.sq = ''''
SELECT @.ret = replace(@.str, @.sq, @.sq + @.sq)
RETURN(@.sq + @.ret + @.sq)
END
----

>

Quote:

Originally Posted by

So running

>

Quote:

Originally Posted by

DECLARE @.sql nvarchar(MAX)
SET @.sql ='SELECT foo FROM foostable'
SET @.sql = dbo.uQuoteString(@.sql)
EXEC sp_executesql @.sql

>

Quote:

Originally Posted by

I now get: Incorrect syntax near 'SELECT foo FROM foostable'

>

Quote:

Originally Posted by

Note that the error has changed, no longer referencing the stored proc
but instead the @.sql argument.

>
I added a PRINT @.sql to your SQL batch, and this is what I saw:
>
'SELECT foo FROM foostable'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SELECT foo FROM foostable'.
>
A string on its own is not legal T-SQL.
>
I don't really know what you want to achieve with your quotestring
function, but you put the entire SQL statement in quotes, which
certainly is not the right thing. You said you were reading statements
from a table. I don't really see why you would double any quotes in
these statements either.
>
Another issue is that the operation is certainly unsafe if anyone can
put statements intos this table, and you run your process with
heavy privs.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||manstein (jkelly.admin@.gmail.com) writes:

Quote:

Originally Posted by

cool thanks. BTW what other declarations allow the use of MAX for
size? I tried char and that did not work. That being the case, isnt
this inconsistent implemetation? MS at its best.

Since char is fixed length, char(MAX) would imply a data type which is
always 2GB in size. I suspect that such a type would do more harm than
good.

In SQL 2005 you can use varchar(MAX), nvarchar(MAX) and varbinary (MAX).
These are the successors to text, ntext and image, which now are
deprecated. The MAX types works very much like the regular
(n)varchar/binary. In difference to the old types that had lots of
limitations.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx