Adding Blank fields

Question

Post reply

Adding Blank fields

stricknyn

SSCertifiable

Points: 6382
More actions
April 24, 2008 at 6:27 am

#120984

Hello everyone,

I am creating a comma delimited text file for a client and they have like 15 blank fields they want in the extract. Don't know why they want blacnk fields, but they do..lol.

Anyways, these blank fields do not come from the data source. I know I can just use the derived field transformation task to just create the field, but then I'd have to do it like 15 times for all the fields. Is there a better way to do this without having to add all the derived field tasks? I was thinking that I can do it by defining the fields in the detination transformation task, but I'm not sure if this can be done here. Anyone know the best practice for adding blank fields?

Note: I'm also trying to avoid putting blank colums in the source SQL statement.

Thanks,

Strick

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dan Colbert-387424 SSCrazy Points: 2195 More actions · Answer 1

Is your source a SQL table? If so, are you using a SQL query for the source query (rather than table or view)? I always try and use a "field-specific" SQL query for my source in case the source table's structure changes.

If so, you can include the blank fields in your original query (i.e. "SELECT '' as Field1, '' as Field2").

That way the blanks are there from the beginning.

Hope this helps...

Dan

stricknyn SSCertifiable Points: 6382 More actions · Answer 2

Yes, my source is a SQL statement. In my Note: I mentioned that I was trying to avoid putting blank colums in my SQL statement. I want the SQL statement to do what SQL is inteneded to do, which is pull data. The blank columns I want SSIS to handle creating. I know I can create blank columns by having derived column tasks, but I have to do so many and it seems there should be a better way to do it than this.

Thanks,

Strick

Dan Colbert-387424 SSCrazy Points: 2195 More actions · Answer 3

Oops - missed that last note... :blush:

I think including the blank values in your SQL source statement IS having SQL do what it is supposed to do. Your client wants several columns of empty values which (at the risk of splitting hairs) is data.

Including it there makes the most sense to me because of the overhead the derived column task adds. I can't think of anywhere else it would fit.

I would be interested in hearing what your final conclusion is.

Dan

stricknyn SSCertifiable Points: 6382 More actions · Answer 4

stricknyn

SSCertifiable

Points: 6382

April 24, 2008 at 8:05 am

#806596

Hello,

Thanks for your assistance. And yes I'll gladly post my solution

Strick

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

You will probably get more overhead adding it into the source SQL than you will adding it in through a derived column component. Adding it into the source means you need to drag the columns thr entire way through your data flow.

If the fields are all together, I would add them as a single column.

If you add a column with the value ",,," (without the quotes) you will get 4 blank columns in a comma delimited file - I'll let you do the math for your situation.

stricknyn SSCertifiable Points: 6382 More actions · Answer 6

stricknyn

SSCertifiable

Points: 6382

April 24, 2008 at 1:55 pm

#806878

Hello,

Here is the solution I used:

Basically instead of defining blank columns in my SQL statement, I was able to simply define extra fields in the flat file destination task. It runs great and is fast.

Thanks,

Strick