Script to change a connection manager

I am looking for code or assistance on how to modify an existing conneciton manger via a script component. I want to have a script change the column sizes for an existing connection manager. How can I get a specific connection manger to change its values.

I have looked at the connection manager, connections, etc classes but I am not certain how to do this as .NET is not my cup of tea.

Thanks.

Once set up though, you can't change column sizes because it will break any associated metadata in the package.|||

Not what I wanted to hear....

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

|||

1Dave wrote:

Not what I wanted to hear....

Well, SSIS can't just dynamically adjust itself depending on any changes to the source. Just not going to happen. Too many components rely on knowing the metadata in advance.

1Dave wrote:

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

Package configurations can change connection strings at run time. You can use an XML file, SQL Server based configurations, etc... You can also override connection strings on the command line, I believe. If you choose to store a connection string in a variable, that variable can be overwritten via the command line as well.|||

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

|||

1Dave wrote:

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

You could treat it as a variable column file and parse it manually inside your data flow with either script or a complex derived column. The connection manager would read the whole row as a single column.

Or, following your original path, you could use code to modify *another* package and execute it. Your original approach won't work because you can't modify the current package. You can modify another package and run that one against your file.