Flat File source to SQL Server Destination question

Question

Flat File source to SQL Server Destination question

themissnlink

SSC Eights!

Points: 969
More actions
September 2, 2008 at 7:24 am

#131007

I am new to SSIS so this may be an obvious question to some.
Is there a way to split a Flat File Source into multiple SQL Server Destinations? A conditional split is not what I am looking either (I think).
The file is actually going to the same destination but I need to split it into more than 1 destination due to the way the text file is set up. It has 4 columns at the end of the file that has multiple payroll id's. Lets say payroll_id1, payroll_id2, payroll_id3...and so on. I need to put all these payroll_id's into a single column called payroll_id into the database.
What I am doing at the moment is just making numerous data flows for each column coming out the text file.
Is there a way to avoid doing that?

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

So I think you are trying to create 4 records for each record coming from your text file.

For this, you can use a Multicast component in your data flow - it just duplicates your data set as many times as you want.

themissnlink SSC Eights! Points: 969 More actions · Answer 2

Does multicast work when its going to the same table? I believe I tried to do this method once and it keep giving me an error.

I will try to run this method again just in case.

Thanks for the reply.

themissnlink SSC Eights! Points: 969 More actions · Answer 3

I just tried that method and it gives me a null error everytime. I have no idea why. I have read the error log over and over and it makes no sense to me at all. The first destination in the multicast works but the second pops the error.

If I go in and do multiple data flows it always works fine. It's just alot of things to run at once.

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

You can multi-cast and then insert into a single table.

You have options here as well. You could use a UNION ALL to merge your Multi-cast output together into a single data set and then use a single destination component. You could also use several destination components (making sure they do not lock the table).

themissnlink SSC Eights! Points: 969 More actions · Answer 5

I just tried the Union All and it gives me the same null error as before. As I said I am rather new to SSIS so maybe I am simply doing it wrong. Here is my process thus far.

Flat File Source > Lookup (for foreign key constraint) > Union all > Multicast > 2 SQL Server Destinations (Same table)

As always the first destination works, but the next does not and gives me a null error.

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

If you are going to use a UNION ALL, it would go after the MULTICAST so you only need a single Destination component.

None of the components you are using should be causing NULL to be created unless you specified allowing missed lookups in the Lookup component.

themissnlink SSC Eights! Points: 969 More actions · Answer 7

I tried putting it after the MULTICAST but it doesn't allow me to do my destinations correctly because I can't select more than one on each column. For example:

Column 28 > Payroll_id

Column 29 > Tax_id

Column 30 > Amt_id

Column 32 > Payroll_id

Column 33 > Tax_id

Column 34 > Amt_id

and so on 15 times in a row until I reach column 86. This is why I do multiple destinations. I simply don't know how to do it any other way.

When I try to do a single destination I can't make these relations because it doesn't allow more than one per column. Maybe I do not understand UNION ALL very well.

Sorry if I am being bothersome. I am intermediate at SSIS on my best day.

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

Yes, you are misunderstanding the UNION ALL entirely.

I would suggest you take a look at Books Online or pick up an SSIS book before you get into anything complicated. For what you are doing now, you should have all 4 of the individual columns coming out in the same column of the UNION ALL. One of the best ways to do this is to use a Derived column on each of your 4 data flows after the multi-cast.

SrikanthSv Ten Centuries Points: 1197 More actions · Answer 9

Please check your mapping in destination

If it says it cannot insert a null value then probabaly the destination mapping is incorrect or the values coming in data flow are blank.

Regards
Venkat
http://sqlblogging.blogspot.com[/url]

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 10

There Might be several issues As many of them have already pointed out .

May be ur lookup is not working.

Have you configured your lookup for Error rows. what happens if there is no match for lookup?.

Also, Look at ur destination table , do they allow nulls, or they might have defaults. if thats teh case , don't check mark in the option of sql destination as "allow nulls". Or try working keep nulls in the source too.

If I were you and all this doesn't work . Download a trash destination my http://www.sqlis.com. Add dataviewers in each pipeline and see what data are coming after each transformation. If null value are coming then default the value to something so that there is no null when you do insert into table.

Thanks,

themissnlink SSC Eights! Points: 969 More actions · Answer 11

Thank you for all your posts. I will try each method listed and see what I come up with.