Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Overwriting entry in table

Task:

To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.

What's the quickest/cleaniest way to do this ?

thanks,
ClaytonUPDATE <table>
SET <column>=<value>, etc...
WHERE field_id = <field_id>

Overwrite flat file destination does not work

I have 3 packages that run consecutively in a main package. Each of these packages read data from a flat file and import it into the Database and then perform some updates and then I use a script component to write the data to a flat file destination and have overwrite flag on the flat file destination to false, however the flat file is not being appended to. All the 3 packages are set to wirte to the same flat file destination each package overwrites the content of the flat file created by the previous packages.

I am wondering why the overwrite = False does not work on the flat file destination. Is there something else that I need to set or is this a defect? Any inputs will be much appreciated.

Thanks,

M.Shah

It works fine for me, I can append to an exiting file from multiple packages. Double-check all your destinations for the setting. I am using SP1 + Hotfix, but I don't remember this being an issue before or having been fixed.|||

I have double checked and I have Overwrite = False for all the three flat file destinations and the flat file still gets overwritten. I have SP1 with no hotfix.

Any further inputs will be much appreciated.

Thanks,

M.Shah

|||I got it to work. Not absolutely certain on what was wrong.