Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What task or combo of tasks can I use in ssis 2015 to load different file names into different tables into sql server?

Posted on 2016-09-28
7
Medium Priority
?
99 Views
Last Modified: 2016-10-17
I have a for each look container using folder and filename variables.  The initial dataflow task in the for each loop container does a conditional split on the file name (2 different file names) and inserts into a seperate initial staging table for each file.  The inital data flow has 2 precedence constraints with expression and constraint for the filename and then processes to seperate subsequent dataflows for each file.   Dataflow for file1 is reprocessing 1 extra file.  screenprint attached.
packageflow.docx
0
Comment
Question by:conardb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41820145
What exactly is your question here?
0
 

Author Comment

by:conardb
ID: 41820371
Is using a dataflow task with conditional split on filename(s) to process different files with different layouts a proper approach to use ssis tasks?    What is your expert opinion?
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41822454
If it works and is fast enough, use it. If you think you might need to support additional filenames in the future, you might want to consider what would facilitate doing that.

You mention both data flow tasks (shown in the attachment) and conditional splits, which are transformations within a data flow task. What is the purpose of testing the filename at both levels?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 41822459
Not exactly.  Data flows are meant for a single source definition and single destination definition, so if your 'different layouts' implies multiple definitions then this needs to be handled using conditional split logic and multiple data flow tasks (aka data pumps) for each unique source/destination definition.
0
 

Author Comment

by:conardb
ID: 41825372
ok, thanks... Presently, I have 1 dataflow that is using conditional split logic to select by file name from the same directory each of 2 different files / formats and write to a different staging table for each.  Then seperate dataflows for each of those types derive the fixed length non-delimted row into  seperate sql server tables.  It's working but I suspected not best practice as the filter by name is redundantly used in the initial dataflow then afterward as a precedence to direct processing to derive to seperate tables .  Jim, could you provide an example or link to an example of your approach?  Thanks again.
0
 
LVL 16

Assisted Solution

by:Megan Brooks
Megan Brooks earned 1000 total points
ID: 41825809
Looking back at your original attachment, it looks like you have a task flow that uses conditional constraints to select one set of sequential data flows or another (conditional split is a data transformation component, not something you would use in the task flow).

Now that I understand the terminology substitutions, the logic looks OK. I only see one set of conditional constraints in the diagram, and there shouldn't be any redundancy in that. Both expressions will be evaluated, but if they are complementary then only one path will continue on for each iteration. Because you are working with constraints, not branching logic, that's what you need to do to use this design.

It seems like it might be clearer if not easier to run two separate for-each loops, eliminating the conditional constraints, and adjusting the file patterns accordingly.

Is the reprocessing that you mentioned initially intended or unintended? Is there a bug to be addressed?
0
 

Author Closing Comment

by:conardb
ID: 41846805
sorry I took so long, thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question