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

pad the generated flat file with 0

Hi Guys,

Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.

I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.

Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.

Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.

What would be the best component to use script component or derived column?

Ta

Gemma

Gemma,

Not sure if this will help or not, but it is how i've padded zero's before...

Use a derived column:

@.LENGTH is the length you would like to pad to)

@.MyStr is the character expression or string you would like to pad

SUBSTRING(REPLICATE("0", @.LENGTH), 1, (@.LENGTH + 1) - LEN(TRIM(@.MyStr))) + TRIM(@.MyStr)

|||

Here's another - using the same variables as above:

RIGHT(REPLICATE("0",@.Length) + @.MyStr, @.Length)

|||

Hi Guys,

I don't know whether this is completely what I asked.

I would need to pad the values dynamically. By this I mean the padding would be done depending on the actual length of data in the columns. So when the file is generated I would have to check each column and if certain column's data length is less then fixed length then pad it with zero.

Other then that how would I suppose to use these variables?

That is why I asked Script or derived column which one.

Just a quick note, the file I'm generating is going to be fixed width as i guess it and i'm generating a dummy file but with fixed width it's not putting the records in each line. Instead file is having all the records at the same line. Why? I've already checked. There are 2 columns which i'm writing. The first i'm putting in as 20 and the 2nd one as 35 width still i'm getting this problem.

Ta

Gamma

|||

The above expressions do exactly what you asked. The variables are simply placeholders in the code. If you know that your column is 20 wide and you want it to pad out the non filled data to 20 places (to the right) you would simply put your column name in the @.MyStr and 20 in the @.Length.

for example we have a membership number that needs to be padded out to 9 digits. The column's name is Member.

SUBSTRING(REPLICATE("0", 9), 1, (9+ 1) - LEN(TRIM([Member]))) + TRIM([Member])

So, what that does is takes a string of 9 zero's (Replicate("0", 9) and substrings out the 1, to 9 - trimmed column length and then adds the trimmed column information.

i.e. member number of "12345 " would yeild substring(replicate("0", 9), 1, 10 - 5) + "12345" > "0000" + "12345" > "000012345"

You would need to set this up for each column seperately.

hope this helps.

sql

Pad Spaces in FFConnection properties?

This is a considerably LOW priority, but is there a way to padd spaces in ssis flat file connection properties? We have cases where a CSV file will read in and throw a truncation error because the field length is greater then defined. However, the CSV file might look like ,"ABC ", where a number of trailing spaces are found. Is there some way to handle this without having to adjust teh field length, maybe ignore whitespace at the source?There is no way at the Flat File Source to ignore trailing whitespace, however there is a work around. Say in this case your field length is 3, and if there are trailing spaces after that, you want to ignore them. You can then use "Ignore Failure" for Truncation Error Disposition. That is, in the Error Output tab for the Flat File Source, for Truncation of this column, choose "Ignore Failure", instead of "Fail Component". By doing this, it will only get the specified characters, and ignore the rest, whether they are trailing white spaces, or some other data there. You have to make sure that this is not causing you to accept some data which you otherwise wanted to fail on really.|||

That is partially the problem, we only want to ignore whitespaces, but if there is actual chars then we would want to throw truncation error.

Thanks for your thoughts...

Package Validation Error

Hi,

I am using Look-UP Transformation to do ETL from a Flat file to a SQL Server Fact Table doing Keylookups from different Dimension Tables:

I am using two connections in the Connection manager, One for the FlatFile and one for the SQLServer Table..

While Executing the Package, I am getting the following error:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: The "runtime connection "OleDbConnection" (110)" in the connection manager collection, Connections, of "component "OLE DB Destination" (102)" does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "OLE DB Destination" (102) failed validation and returned error code 0xC004801C.

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Can anyone help me ?

Thanks in Advance,

Sundar

I guess this might help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=108043&SiteID=1

|||Make sure that in the flat file source and the destination that you have actually chosen a connection manager.

You'll get this error if you don't chose a connection manager.|||

Thanks a lot Phil and Unni.

Once I configured the connection Manager, Package worked like a charm !!

Cheers,

Sundar

Package Stuck on "Pre-Execute phase is beginning."

Hi all,

I have a monster SQL query (SQL Server connection) that I'm trying to export to flat file. My package appears to just stick on the "Pre-Execute phase is beginning" stage. (I left it running overnight, and it's still going... no error message, I think it's just hung. If I include a "Top 10" in the my source query it completes without a problem. I'm wondering if it's an out-of-memory issue, but when I've run into this in the past there have been error messages generated. I've tried using both a Data Reader and an OLEDB Datasource. Does anyone have any ideas of what's going on and how I might fix it?

Thanks!!!

JessYou should run the query in management studio and look at its explain plan... Chances are that it has nothing to do with SSIS. Perhaps you have locks in place that are blocking parts of this query such that it never completes?

You could always wrap the query into a view and use the view as the source to your package. That might help the pre-execute phase.

Also, try setting your DelayValidation property to TRUE for the connection manager that's being used for this query.|||

Thanks for your response. I finally got it working. Our off-shore team actually wrote the query for this project and it was fairly convoluted. I just have the pleasure of actually trying to RUN it.

Anyhow, there was no descernable blocking from sp_who2 'active.' While switching the source query over to a view (per your suggestion), I discovered that the source sql inserted a number of records into a (non-indexed) table variable via a cursor. Placing this data into a permanent, indexed table seems to be the change that got the package working.

I've done some more testing, and whenever I use a table variable in the source query I seem to get stuck at "Pre-Execute." Is this by-design?

Thanks again for your input.

Jess

|||

Hi Jessica,

It is never by design to get stuck.

Pre-execute is the time when your queries get prepared and all the problems in them are likely to show up at that point. Looking at the SQL Profiler might help you figure out what exactly is causing issues while queries are processed.

HTH.

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.