question

JamesMcKee-0197 avatar image
0 Votes"
JamesMcKee-0197 asked Monalv-msft edited

SSIS Sequence Container question

Hello,

First, pardon my ignorance. I am a core system admin at a large financial institution. I am not a formal SSIS developer. I have, however, worked with SQL for a long time and trained myself in SSIS. If my question is not clear, it is of my own ignorance. Any advice is appreciated. This audience is very skilled!

I will describe the need first for better understanding

Data from an outside party needs to be ingested into data "lake", for lack of better term.

Data is in the form of applications completed by borrowers. As such there are many leaf objects to this set.

The data arrives in the form of XML. The target is SQL server

Using Microsoft SQL Server Data Tools for Visual Studio 2017 to craft the logic

First step was a construct to strip the namespace from the XML files

Using the XML I created the equivalent of the app in a SQL server database. 105 tables

I have completed the receiving schema. I have 105 packages. I have ingested apps manually to test.

Now I believe it may be appropriate to use sequence container to parse the application XML files one at a time. Here lies the question. Is this the best way to proceed?

I appreciate any insight and again, I apologize again for my ignorance.

Have a great day, or evening.

Jim

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @JamesMcKee-0197 ,

Could you please share the screenshots in your SSIS packages?

Please refer to the following links:
1.Sequence Container
2.Sequence Containers in SSIS packages
The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.

3.Execute Package Task
4.SSIS - Execute Package Task
The Execute Package task extends the enterprise capabilities of Integration Services by letting packages run other packages as part of a workflow.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JamesMcKee-0197 avatar image
0 Votes"
JamesMcKee-0197 answered

Thanks for the valuable insight. Much appreciated. Have a great day, or evening. Jim

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

Hi @JamesMcKee-0197,

Now I believe it may be appropriate to use sequence container to parse the application XML files one at a time. Here lies the question. Is this the best way to proceed?

It is not clear what you are asking.
Please edit your post and add a screen shot of one of your packages.

Usually SSIS Foreach Loop Container is used to process XML files one at a time.

ssis-foreach-loop-file-enumerator

UPDATE

I meant to say I need to fire all the packages at once.

You can create a master SSIS package where you can put a Sequence Container with lots of Execute Package Tasks. And no Control Flow lines between the tasks inside the container.
This way it will be a parallel execution. Just be careful with the resources on the machine.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

II apologize for my rambling dissertation. I made an error in my fact sheet. I meant to say I need to fire all the packages at once. I do not care about an order that they fire. I understand if I place them all inside the container they will fire 1 at a time until complete? Each application will write data to the entire schema. I will use a loop to read the application (XML) files into the container. Since I have never used multiple packages in the same project I am just wondering if the container method is the best use case. I think it is. Thanks and pardon the absence of needed information. Thanks

0 Votes 0 ·

@JamesMcKee-0197,

I updated the answer. Please check its UPDATE section.

0 Votes 0 ·