Changing Column length dynamically

Question

HI.
I have a table which contains few columns which are of fixed length. The source from which I receive the data - the data is getting changed every time with differing length for the columns.
This results in SSIS jobs to fail. Is it possible to increase the column length dynamically depending on the data being received ? what are the other ways to resolve this, if the dynamic column is not a possibility?
Thanks

Answer 1

Is the column data type CHAR = fixed length string? Then use VAR CHAR ( var = variable length) with a appropriate length instead.

Answer 2

You first said that the length is fixed and then said it is varchar (which is variable). Which is it? Char or varchar?

Anyhow, assuming it is varchar, and not char. I.e., it isn't fixed it is variable with a max length of 100. What you need to do is to specify a max length that satisfies your needs. That is what varchar is for! If you need max 1000, then specify marchar(1000). Etc. It is as simple as that!

Answer 3

SSIS requires known field names and sizes at design time. Those values cannot be changed during runtime.

You would need to allow for the max values to do what you are trying to do in SSIS.

Answer 4

Hi @Padmanabhan, Venkatesh ,

It is the Varchar column of 100 length.
The data which the column is receiving is changing . sometimes the column gets data with 50 characters, sometimes 500 , sometimes 1000. The data varies

We can use the following sql query in SSIS Execute SQL Task to change the datatype of column from varchar(100) to varchar(8000) or varchar(max).

ALTER TABLE YourTableName ALTER COLUMN YourColumnName Varchar(8000);
GO

Please refer to char and varchar (Transact-SQL) and changing the size of a column.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.