SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS : How to pass the value of two arrays into a variable


SSIS : How to pass the value of two arrays into a variable

Author
Message
Thom A
Thom A
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80258 Visits: 20361
river1 - Monday, April 16, 2018 7:42 AM

Hello,

Thank you for your reply. Not sure I undertood your answer.

Maybe what I will do is continue with my approach. but instead of saving the content into a variable a will save it into a csv file. Then another package with read this file.

Cheers,



There are 2 Script items in SSIS. The first is a Script Task, which is in the Task Flow. The other is a Script Component, which is used within a Data Flow task. A Script Component can be a Source, Transformation or Destination. The advantage for what you want to do here is that you don't need to load your data to an object variable; you can use it as an actual source just like you would with a flat file source.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
river1
river1
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20840 Visits: 1799

Thank you very much.

I guess I found a cool solution that I would like to post here.

I have created two connections, one called "ResColumn" and Other "WithoutResColumn"

Then I have created for each a data flow.

Basically when the first one fails (meaning that the file has NO res column ) I send the flow to execute the other data flow.

So on failure of flo1 move to Flow 2

Wink

Cheers


river1
river1
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20840 Visits: 1799
Now my problem is. I would not like that if Dataflow 1 fails that the package fails. Instead I would like to wait for the result of data flow 2 to give the package as failed.

Can someone tell me how can I do this?

Basically:

If data flow 1 sucess then package sucess
If data flow 1 fails move to data flow 2 but don't give the package as failed
if data flow 2 fails give the package as failed otherwise (if sucess), as sucessfull.

Thanks
Thom A
Thom A
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80258 Visits: 20361
river1 - Monday, April 16, 2018 9:15 AM
Now my problem is. I would not like that if Dataflow 1 fails that the package fails. Instead I would like to wait for the result of data flow 2 to give the package as failed.

Can someone tell me how can I do this?

Basically:

If data flow 1 sucess then package sucess
If data flow 1 fails move to data flow 2 but don't give the package as failed
if data flow 2 fails give the package as failed otherwise (if sucess), as sucessfull.

Thanks


Change the value of the FailPackageOnFailure property on the DataFlow task to False.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 24525
river1 - Monday, April 16, 2018 9:09 AM

Thank you very much.

I guess I found a cool solution that I would like to post here.

I have created two connections, one called "ResColumn" and Other "WithoutResColumn"

Then I have created for each a data flow.

Basically when the first one fails (meaning that the file has NO res column ) I send the flow to execute the other data flow.

So on failure of flo1 move to Flow 2

Wink

Cheers


The better solution (IMO) is to have a first step which checks the file to determine whether it contains the troublesome column & set a Boolean package variable accordingly, and then to use precedence contraints to control which DF gets executed.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
river1
river1
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20840 Visits: 1799
I decided to move with a different approach:


I have an SSIS package with two different connections and two different file flat file sources inside the same data flow.

One connection try to read the file with RES column and the other Without the RES column

Both of the flat files sources connect to the same UNION ALL.

Issue is that if one of the sources gets an error, then the other is not able to move data after the union all Sad

I was expecting that, if one source fails and the other sucesses (as it is occurring) then I will be able to continue. But problem is that even with datasource 2 succeding and sending 3 rows into the union all (as per the data viewer) then data is not moved from union all into the next task.

Can you help me understand how can I change this?

Thank you
Thom A
Thom A
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80258 Visits: 20361
Personally, i think Phil's suggestion earlier is the winner. Check if the rogue column exists first, if it doesn't use the dataflow that doesn't require it, if it does, use the one that does have it.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
river1
river1
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20840 Visits: 1799
Thom A - Monday, April 16, 2018 9:20 AM
river1 - Monday, April 16, 2018 9:15 AM
Now my problem is. I would not like that if Dataflow 1 fails that the package fails. Instead I would like to wait for the result of data flow 2 to give the package as failed.

Can someone tell me how can I do this?

Basically:

If data flow 1 sucess then package sucess
If data flow 1 fails move to data flow 2 but don't give the package as failed
if data flow 2 fails give the package as failed otherwise (if sucess), as sucessfull.

Thanks


Change the value of the FailPackageOnFailure property on the DataFlow task to False.


Thank you very much for your reply. I cannot do that because both flat file sources are in the same data flow. So what I have is: One SSIS package with two connections, one named WithResColumn and the other named WithoutResColum. Then, inside the same data flow I have two flat file sources. One pointing to one connection and the other positing to the other. Both flat file sources connect to the same UNION ALL task. I would like that if one fails the other would continuo. Problem is that, If one of the flat file sources fails, then , even the other succeding , the data is not passed from the union all onwards. What can I do?
river1
river1
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20840 Visits: 1799
Thom A - Tuesday, April 17, 2018 4:37 AM
Personally, i think Phil's suggestion earlier is the winner. Check if the rogue column exists first, if it doesn't use the dataflow that doesn't require it, if it does, use the one that does have it.


That will require me to have to separated data flows with the same code. So not so easy to maintain.

I would prefer to have just one Data Flow with two different data source flat files inside.

This is way I would be very pleased if I understand what can I do to guaranty:

Both data sources connected to the same Union all. If one fails, the other should continue the process without problem... Problem is that at this stage, if one fails the other is not able to proceed afetr the union all task Sad
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211219 Visits: 24525
river1 - Tuesday, April 17, 2018 6:41 AM
[quote]
Thom A - Tuesday, April 17, 2018 4:37 AM
Personally, i think Phil's suggestion earlier is the winner. Check if the rogue column exists first, if it doesn't use the dataflow that doesn't require it, if it does, use the one that does have it.


That will require me to have to separated data flows with the same code. So not so easy to maintain.

Well, yes, but I checked what you wrote in an earlier post:

I have created two connections, one called "ResColumn" and Other "WithoutResColumn"

Then I have created for each a data flow.


So ... each connection has a data flow. I was just using the information which you told us.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search